range data type

[seanw]

how do I set a range's data type using VB? I wrote a vb
app to parse data into a csv file. Then I open the csv
into excel and populate the columns. Now I need to set
the columns to text instead of the numbers being treated
like numbers.



thx

 

[Tom Ogilvy]

You don't

you can format a cell, but since your value is already stored there, it will
have no effect.

columns(1).NumberFormat = "@"
formats a column as Text.

To make the numbers text after the fact you would have to do

With Activesheet
for each cell in Intersect(.Columns(1),.UsedRange)
if isnumeric(cell.Value) then
cell.Value = "'" & cell.Value
' or cell.Value = "'" & cell.Text
end if
Next
End With

 

[Tod]

Generally, if you set the format of the cells in the
worksheet to Text and then PasteSpecial Values the CSV
file in, the numbers should format as if they were text.