Convert Text to Numbers via VBA

[Snap Happy]

I'm sure my issue has been covered before but I can't find the solution I need.I receive an Excel report from a third party where some of the columns are 'Number Stored as Text'. I can convert these manually using the highlight and 'Convert to Numbers' on the drop down but I need to do this automatically using VBA code.I select the fields usingRange(Selection,Selection.End(xlDown)).Selectbut can't figure out the next step to convert to numbers.Can anyone help?

 

[VoG]

Try selecting them, Data > Text to Columns and click Finish. You can record a macro whilst doing this.

 

[saltkev]

Hi

Try this, it will work under a command button.

For Each Convert In Range("B4:b10")
Convert.Offset(0, 1).Value = "=VALUE(RC[-1])"
Convert.Value = Convert.Offset(0, 1).Value
Convert.Offset(0, 1).Delete
 
 
Next Convert

regards

Kev

P.S Range B4:B15 is the range of date to change, alter to suit

 

[rbartholomew]

VoG's Data > Text to Column method worked for me! Thanks.