How to prevent a number stored as text from converting to a number

[Aaron Rubinstein]

I have numeric accounting codes in a worksheet that are stored as text
- each cell has a little green error triangle in the top left
indicating the number in the cell is formatted as text. When I execute
the VBA code below (assume the accounting code stored as text is the
Active Cell), the value that's input below the active cell is no
longer stored as text.

Sub Macro1()

Dim Temp As String

Temp = ActiveCell.Value
ActiveCell.Offset(1,0) = Temp

End Sub

One thing I noticed is that if you convert a number to text in excel
using the TEXT funciton, i.e. TEXT(A1,0), it will result in a number
stored as text, however if you use the TEXT worksheet function in VBA
with the same arguments, it does not seem to produce the same result.

Any thoughts would be greatly appreciated.

Thanks.

 

[Per Jessen]

Hi

You have to format the reciving cell as Text like this:

ActiveCell.Offset(1, 0).NumberFormat = "@"
ActiveCell.Offset(1, 0) = Temp


Regards,
Per

 

[Aaron Rubinstein]

Perfect - thanks!