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

Ron Rosenfeld

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