vba create text format cells in excel 2003

[douglas]

I need to put char(5) into 'selected' columns in an excel 2003 worksheet
using VBA.
The problem is some of the data looks like '00123' and ends in the
spreadsheet looking like '123'. I am losing the leading zeroes. The only way
I know to make the selected range of columns is to make the selected columns
a 'text' format.
Thus using VBA can you tell me how to do the solve the problem above:
1. Do I set the selected columns to 'text' fomrat and then input the
data? If not,
2. Do I put data into the selected columns and then change the data to a
'text' format?

Thanks!

 

[Jacob Skaria]

Range("A1").NumberFormat = "@"
Range("A1") = 10

OR
Dim lngTemp As Long
lngTemp = 10
Range("A1").NumberFormat = "@"
Range("A1") = Format(lngTemp, "0000")

If this post helps click Yes

 

[douglas]

what does Range("A1").NumberFormat = "@" mean?

Range("A1").NumberFormat = "@"
Range("A1") = 10

OR
Dim lngTemp As Long
lngTemp = 10
Range("A1").NumberFormat = "@"
Range("A1") = Format(lngTemp, "0000")

If this post helps click Yes

 

[Jacob Skaria]

Format cell to Text

 

[Tim Zych]

Alternative to the Text format, you can use a custom format of "00000". Then
the cell(s) can be SUMmed if need be.

Or, a more granular approach of preceding the value with a single quote,
e.g. '00123.


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

http://www.higherdata.com/sql/batchsqlfromexcel.html
Create batch SQL from Excel