Writing Out Numbers as Text

[marston.gould]

I have an array that contains several items.
Some are pure text, some are numbers as text, and some are numbers.

I assign the array to a range using:

Set myRange = Range(.......
myRange.Select
myRange = myArray

However, when I do this the items that are numbers as text are
converted back to numbers. I don't want this as its important they be
numbers as text because our ERP system understands those to be things
like division, department, location, etc. identifiers and numbers and
numbers to be $ or people. How can I keep VB from converting them to
numbers during the assignement?

 

[keepITcool]

It will not convert it you READ,
it may convert when you WRITE to cells.

if you must write TEXT
either: (preferred option)
format the cell as "@" text numberformat
before you assign it's value
or:
prefix the value with an apostroph.


Sub Foo()
Dim aValue as variant ,itm as Variant
Range("a1").numberformat ="@"
Range("a2").numberformat ="General"
Range("a3").numberformat ="0"
Range("a1:a3").Value = array("1234","'1234",1234)

aValue = Range("a1:a3").Value
For Each itm In aValue
Debug.Print TypeName(itm)
Next

End Sub.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >