ExcelBanter - View Single Post - Convert Text Formated Numbers into Numbers all in VBA

Marston

Ron Rosenfeld wrote in message . ..
On 5 Aug 2004 12:51:32 -0700, (Marston) wrote:

Hi -

I have a set of data that is coming out of an ERP system - and in many
cases, it changes the numbers in text. I have a routine that works by
writing the value "1" to a cell in the worksheet, then multiplies the
entire range of data by 1 using:

Selection.PasteSpecial Paste:=xlValues, Operation:=xlMultiply,
SkipBlanks _
:=False, Transpose:=False

Is there a way of doing this all in VBA w/o setting a cell to the
value of 1?
I'd like to take a range selection multiple it by 1 and put it in the
same place on a worksheet from which it came before being multiplied.

Thanks

Marston

==================
Sub TextToNum()
Dim c As Range
On Error Resume Next
For Each c In Selection
c.Value = CDbl(c.Text)
c.NumberFormat = "General"
Next c
End Sub
====================

--ron

I just tested this - and unfortunately, it didn't work.
The numbers that were created as "text" stayed that way.
Should I set c.Value = cDbl(c.Text)*1 ?

As a complicating factor - some of the items in the data are numbers
not text,
so I probably don't want to run this on those, just the items that are
numbers as text.