Solved: Newbie...Stopping automatic conversion to scientific notation in VBA

[flipatibm]

I have product id's that in one instance, I'm just moving from one sheet to another and no conversion to scientific notation occurs. Product ID example: 5565E23. When I subsequently read that product id into a string array from a sheet and then, after some work, place it on another sheet, it gets converted to scientific notation. The destination cell is formated as text. I just want it to come out as it went in; 5565E23.

Any help is appreciated.

[Cyberdude]

Hi, flip! Welcome to VBAX, where we will to our best to confuse and amuse you!

When I started fooling around with your text string, I had no problems IF the target cell had been preformatted as Text.

The problem is that Excel makes its own decision about how to format anything that is written into an unformatted (default) cell. It does a pretty good job of deciding what kind of value you write, but you just happened to be writing something that looks like scientific notation, soooo . . .

I think your problems will go away if you spend a few seconds preformatting all the target cells BEFORE you write into them. It's a nuisance, but try it and see if that helps.

Sid

[Bob Phillips]

You can also force it in your code

[vba]

With ActiveCell
.Offset(0, 1).NumberFormat = "@"
.Offset(0, 1).Value = ActiveCell.Text
End With
[/vba]

[flipatibm]

Thank you. Works great.

[mdmackillop]

Hi flipatibm
Welcome to VBAX
If your question is solved, please mark it so using the thread tools dropdown
Regards
MD