Convert Text Formated Numbers into Numbers all in VBA
 

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

Convert Text Formated Numbers into Numbers all in VBA
 

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

Convert Text Formated Numbers into Numbers all in VBA
 

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.

Convert Text Formated Numbers into Numbers all in VBA
 

On 6 Aug 2004 10:15:37 -0700, (Marston) wrote:

I just tested this - and unfortunately, it didn't work.
The numbers that were created as "text" stayed that way.

It worked properly here using numbers entered as text. There must be something
about your data of which I am not aware, or about the method you are using to
enter the subroutine.

Please post a sample of data that will not convert. And also, on a
non-converting value, post the output of the following:

With a non-converting data in A1:

=LEN(A1)

Then array enter the following formula:

=CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

To *array-enter* a formula, hold down <ctrl<shift while hitting <enter. XL
will place braces {...} around the formula.

After you enter the array formula, place the cursor in the formula bar and
<F9. This should result in a highlighted set of numbers in the formula bar
looking something like: {32;49;32;160}

Hit <ctrl-C to copy that array, then paste it into your response to me.


Should I set c.Value = cDbl(c.Text)*1 ?

I don't know if that would work. But I would rather figure out why the
original is not working.


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.

That should not be a problem. If non-numeric text is selected, the On Error
statement will cause the macro to just "skip over" those entries.


--ron