VBA skip if wrong variable type-CodePudding

I'm looping through a list of items most of which are numbers but occasionally I get a string.

I would like to skip the strings and go to the next row without breaking the loop. I am defining the numbers as doubles so the strings give me a type mismatch errror. I think I should be using some sort of IF test but am unsure how to tell VBA to skip the 'wrong' variables.

I have tried using variants to avoid the error but can't find an IF test to tell them apart.

CodePudding user response：

Here is a tiny example of explicitly testing each value to see if it is "double compatible":

Sub NumCk()
    Dim r As Range, rng As Range, v As Variant, d As Double
    Set rng = Range("A1:A10")
    For Each r In rng
        v = r.Value
        On Error Resume Next
            d = CDbl(v)
            If Err.Number = 0 Then
                r.Offset(0, 1) = d / 2
            Else
                Err.Number = 0
            End If
        On Error GoTo 0
    Next r
            
End Sub

It will reject a text value like "hello world" but accept a value like "1.2" as a text string.

CodePudding user response：

If you want to get only explicit numbers excluding thereby also any text-formatted numbers (NumberFormat = "@"), which would be interpreted as Double anyway, you might code as follows checking for the variable type (VarType) as well as for the NumberFormat:

Sub ExplicitNumbersOnly()

    Dim rng As Range
    Set rng = Tabelle1.Range("A2:A10")
    Dim i As Long
    For i = 1 To rng.Rows.Count
        Dim currCell As Range: Set currCell = rng.Cells(i, 1)
        If VarType(currCell) = vbDouble And currCell.NumberFormat <> "@" Then
            Debug.Print "OK:", currCell.Value
            '... do something
            '...
        Else
            'Debug.Print "Omitted: " & currCell.Address
        End If
    Next i

End Sub