Bizarre mutation of data upon assignment to cell !!??!!

canoe414 · Joined: 04 Oct 2007 Posts: 28

Greetings all,

I've come across somthing truly bizarre - an intermittant case of a
value mutating during assignment!!!

For i = 3 To 1103
---If etch_keyarryColl.KeyExists(netval) Then
------Set etch_keyarry = etch_keyarryColl.Item(netval)
------etch_arry = etch_keyarry.arry
------If Not etch_arry(0) = "EXT" Then
---------xlWrksheet.Cells(i, 6).Value = etch_arry(0) <--- problem
------End If
------If i = 404 Then
---------ii = 4 <--- breakpoint set on this line
------End If
---End If
Next i

At the breakpoint, within the debugger positioning over etch_arry(0)
shows
6/7/9
which is correct. But positioning over xlWrksheet.Cells(i, 6).Value
shows
6/7/2009
which is INcorrect!!!

This mutation occurs not only when i = 404 but over roughly 5% of the
span of i.
The mutation is always an insertion of a snippet of text, never a
deletion.
And I've noticed a fair percentage of the time the snippet of text
added is a
3-character month. Examples:
3 becomes 3-Aug
7 becomes 7-Feb

I've dumped out etch_keyarryColl to a file and verified 6/7/9 is indeed
stored
in the correct place for when i = 404 and that there are no text
snippets resembling
month codes anywhere in the file.

I'm completely stumped! What's causing these intermittant,
not-quite-random mutations
? And more importantly how can I rid myself of them?

TIA,

Steve

Archived from group: microsoft>public>vb>ole>automation

canoe414 · Joined: 04 Oct 2007 Posts: 28

A little more sluething suggests that Excel is trying to "help" me by
interpreting the data
as being a day-of-the-month or a fully-qualified date
1/3 --> 3-Jan
6/7/9 --> 6/7/2009
3/4 --> 4-Mar
6/9 --> 9-Jun
3/5 --> 5-Mar
2/10 --> 10-Feb

So the problem is not in the code but rather in what Excel is doing
with the data I stuff in the cell.

Much easier question now: how to I force a cell's formatting to be pure
plain text w/o any "assistance" from Excel?

TIA,

Steve

canoe414 · Joined: 04 Oct 2007 Posts: 28

> Much easier question now: how to I force a cell's formatting to be pure
> plain text w/o any "assistance" from Excel?

The solution is to add this line:
xlWrksheet.Cells(i, 6).NumberFormat = "@"
just ahead of the assignment line

Case closed.

Thanks to myself, Wink

Steve

miconus · Joined: 04 Oct 2007 Posts: 4

I use a ' prefix on the data

wrote in message @b28g2000cwb.googlegroups.com...
>A little more sluething suggests that Excel is trying to "help" me by
> interpreting the data
> as being a day-of-the-month or a fully-qualified date
> 1/3 --> 3-Jan
> 6/7/9 --> 6/7/2009
> 3/4 --> 4-Mar
> 6/9 --> 9-Jun
> 3/5 --> 5-Mar
> 2/10 --> 10-Feb
>
> So the problem is not in the code but rather in what Excel is doing
> with the data I stuff in the cell.
>
> Much easier question now: how to I force a cell's formatting to be pure
> plain text w/o any "assistance" from Excel?
>
> TIA,
>
> Steve
>