Excel - convert numbers stored as text to numbers 1

[hotbread]

I'm trying to find a way to convert columns of numbers in text format to numbers in number format in code.

Manually this is quite easy - I just highlight the column/s, click the smart tag on the first one, and click "convert to number" which affects all cells in the selected range. I thought I could find the coding for this by simply recording a macro and clicking the smart tag, but it doesn't result in any code.

The Excel help file also has an article with the title "Convert numbers stored as text to numbers", where you copy a cell containing the value 1 and paste special into the range (in this case the selected columns) using the multiply option. This is easy enough to code in VBA, but the problem with this is that any cell in the selected range that is empty ends up displaying 0.

Does anyone know how to code in VBA what the smart tag does in converting text to numbers in a selected range?

I won't go into specifics of why I need a macro to do this, apart from saying it's impractical to do the conversion manually.

Thanks in advance!

Tim

 

[SkipVought]

Hi,

Macro record this...

enter the value 1 in a cell.

Copy.

Select the column you want to convert.

Edit/Paste Special - MULTIPLY.

Skip,

_[red][/red]

 

[hotbread]

Thanks for your input Skip, but as I said I have already tried this and it leads to a different problem where blank cells end up displaying 0, so this solution is unsuitable.

 

[SkipVought]

for column "A", modify as needed...

For each r in range(cells(1,"A"), cells(cells.rows.count, "A").end(xlup))
  With r
   if len(.value)>0 then .value = .value * 1
  end with
next

Skip,

_[red][/red]

 

[hotbread]

Thanks Skip.... that code worked perfectly!