Convert text to numbers

[Conan Kelly]

Hello all,

When working in XL and it finds numbers stored as text, a smart tag pops up
letting you convert the text to numbers.

I'm assuming there is a way to do this programatically.......maybe something
like a "ConverTextToNumbers" method.......yes/no/maybe???

Thanks for any help anyone can provide,

Conan Kelly

 

[Gary''s Student]

try this macro:

Sub numerify()
Dim r As Range
Count = 0
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub

 

[Dan R.]

Conan,

Here's an example:

Sub test()

Set ws = ActiveSheet
Set r = ws.Range("A:B").SpecialCells(xlCellTypeConstants)

For Each rr In r
If IsNumeric(rr.Value) Then
rr.NumberFormat = "General"
rr.Value = rr.Value
End If
Next

End Sub

 

[Conan Kelly]

Gary's Student,

Thanks for the help.

I would have though that there would have been some method part of the Error
object that would have done that automatically.

Guess not!!! That would be too easy!!!

Thanks again for all of your help,

Conan