Converting to Number using VBA

[John]

I have a cell which gives the warning
"The number in this cell is formatted as text."

Using VBA, I change the format of the cell to a number format.
However, Excel still shows the warning.
I look at the cell using Format | Cells ... which says it has
the number format set by my VBA code.

Is there some VBA function I have to use on the cell to make it
convert to a number? Thank you for your help.

 

[John]

I forget to mention the article

HOW TO: Convert Text to Numbers in Excel 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;822665#8

which claims you need to format the cells and then run

For Each xCell In rangeColumn
xCell.Value = xCell.Value
Next xCell

Isn't there just a range method which will do the equivalent
of the For clause? Why doesn't the NumberFormat do this
as a side effect?
I have a cell which gives the warning
"The number in this cell is formatted as text."

Using VBA, I change the format of the cell to a number format.
However, Excel still shows the warning.
I look at the cell using Format | Cells ... which says it has
the number format set by my VBA code.

Is there some VBA function I have to use on the cell to make it
convert to a number? Thank you for your help.

 

[George Nicholson]

Alternative method:
- in a blank cell (formated as a number) insert the number 1.
- copy it.
- Select the offending text cell(s).
- PasteSpecial>Multiply
--
George Nicholson

Remove 'Junk' from return address.

I forget to mention the article

HOW TO: Convert Text to Numbers in Excel 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;822665#8

which claims you need to format the cells and then run

For Each xCell In rangeColumn
xCell.Value = xCell.Value
Next xCell

Isn't there just a range method which will do the equivalent
of the For clause? Why doesn't the NumberFormat do this
as a side effect?
I have a cell which gives the warning
"The number in this cell is formatted as text."

Using VBA, I change the format of the cell to a number format.
However, Excel still shows the warning.
I look at the cell using Format | Cells ... which says it has
the number format set by my VBA code.

Is there some VBA function I have to use on the cell to make it
convert to a number? Thank you for your help.