VBA to convert from Text to Number

[chaddres]

I have a spreadsheet with multiple numbers stored as text. I wish to convert all of the numbers stored as text to numbers. I wrote this code:

Range("A2").Select
Selection.CurrentRegion.Select
If IsNumeric(xCell) Then
For Each xCell In Selection
xCell.Value = Val(xCell.Value)
Next xCell
End If

My problem is that it converts all of my text to 0's when it converts the numeric text values to numbers.

Thank you!

 

[VoG]

Try

For Each xCell In Range("A2").CurrentRegion
    If IsNumeric(xCell) Then xCell.Value = Val(xCell.Value)
Next xCell

 

[chaddres]

Worked perfect, thank you.

 

[vds1]

Hello,

Just came to my mind.. what if i do this way.. Obviously Peter has better solution..

[COLOR=#0000ff]Sub[/COLOR] Without_Loop()
[COLOR=#0000ff]With[/COLOR] Range("XFD1")
    .Value = 1
    .Copy
[COLOR=#0000ff]End With[/COLOR]
[COLOR=#0000ff]With[/COLOR] Range("A2").CurrentRegion
    .PasteSpecial Paste:=xlPasteFormats
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
[COLOR=#0000ff]End With[/COLOR]
Range("XFD1").Value = ""
[COLOR=#0000ff]End Sub[/COLOR]