Numbers Stored as text problem

[Jaazaniah]

Hey everyone,

I've got an automated VBA for two functions of bringing in and
distributing data. My issue is with the source data, which comes in
csv format, and Excel insists on opening it and giving each cell the
text data type. This causes havoc with the formulas and other
functions I've set up around numeric input during development. One
work around that worked until recently (via a mysterious resource
shortage) was this:

Dim Origin as Range

....
Open Input file
....
Set Origin ...
Origin.NumberFormat = "###0"
Origin.Formula = Origin.Value

This now stops half way through on a resource shortage. How do I
address the TextAsNumbers error in Excel such that the cell values are
updated as numeric? Note: simply setting the format is not sufficient,
the cells stay as text until updated.

Any help would be appreciated.

-Jaazaniah Cole

 

[Gary Keramidas]

i've used something like this that works for me
in this example, D1 is some known blank cell, the data numbers stored as text is
starts in cell A2
see if it does what you need.


Sub test()
Dim rng As Range
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A2:A" & lastrow)
ws.Range("D1").Copy
rng.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
End Sub

 

[smartin]

Hey everyone,

I've got an automated VBA for two functions of bringing in and
distributing data. My issue is with the source data, which comes in
csv format, and Excel insists on opening it and giving each cell the
text data type. This causes havoc with the formulas and other
functions I've set up around numeric input during development. One
work around that worked until recently (via a mysterious resource
shortage) was this:

Dim Origin as Range

...
Open Input file
...
Set Origin ...
Origin.NumberFormat = "###0"
Origin.Formula = Origin.Value

This now stops half way through on a resource shortage. How do I
address the TextAsNumbers error in Excel such that the cell values are
updated as numeric? Note: simply setting the format is not sufficient,
the cells stay as text until updated.

Any help would be appreciated.

Hi Jaazaniah,

Could you employ the "paste special | values | multiply" trick for
converting text to number format before you start to parse the numbers?

In this code snippet, I started with A1:C2 filled with numbers but
formatted as text. Cell A1 contained "1":

Range("A1:C2").Select
Selection.NumberFormat = "General"
Range("A1").Select
Selection.Copy
Range("A1:C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply

 

[Jaazaniah]

Hi Jaazaniah,

Could you employ the "paste special | values | multiply" trick for
converting text to number format before you start to parse the numbers?

In this code snippet, I started with A1:C2 filled with numbers but
formatted as text. Cell A1 contained "1":

     Range("A1:C2").Select
     Selection.NumberFormat = "General"
     Range("A1").Select
     Selection.Copy
     Range("A1:C2").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply- Hide quoted text -

- Show quoted text -

Wonderful! Worked like a charm. Thank you very much.