Can't catch an error with on error operaror

August 20, 2024

Sub TextToNum()
Dim af As Double
For Each x In Selection
On Error GoTo 10
af = x.Value 'here appears error
x.NumberFormat = Number
On Error GoTo -1
x.Value = af
10 Next x
End Sub

If the value of the cell can’t be converted to number (letters, symbols) then there appears the error message and macros stops executing. Pass this error via On Error fails -the error message appears any way. How to solve this?

>Solution :

You can use isNumber instead of error checking:

Sub TextToNum()
Dim af As Double
Dim x As Range
For Each x In Selection
    If IsNumeric(x.Value) Then
        af = x.Value
        x.NumberFormat = Number
        x.Value = af
    End If
Next x
End Sub