VBA: How to Use IsNumeric to Check if Cell is a Number


You can use the IsNumeric function in VBA to check if a given cell is a number.

This function will return True if the value in a given cell is recognized as a number.

Otherwise, the function will return False.

Here is one common way to use this function in practice:

Sub CheckNumeric()
    
    Dim i As Integer

    For i = 1 To 9
    
        If IsNumeric(Range("A" & i)) = True Then
            Range("B" & i) = "Numeric Value"
        Else
            Range("B" & i) = "Not a Numeric Value"
        End If
    Next i
    
End Sub

This particular macro will check if each cell in the range A1:A9 is a number.

If a cell is a number, then “Numeric Value” will be returned in the corresponding cell in the range B1:B9.

If a cell is not a number, then “Not a Numeric Value” will be returned instead.

The following example shows how to use this syntax in practice.

Example: How to Use IsNumeric in VBA

Suppose we have the following column of values in Excel:

Suppose we would like to check if each cell in column A is number.

We can create the following macro to do so:

Sub CheckNumeric()
    
    Dim i As Integer

    For i = 1 To 9
    
        If IsNumeric(Range("A" & i)) = True Then
            Range("B" & i) = "Numeric Value"
        Else
            Range("B" & i) = "Not a Numeric Value"
        End If
    Next i
    
End Sub

When we run this macro, we receive the following output:

VBA IsNumeric function example

Column B displays output that tells us whether or not each corresponding cell in column A is recognized as a number or not.

Here are some interesting things to note from the output:

  • Numbers with decimals are recognized as numbers.
  • Percentages are recognized as numbers.
  • Dates are not recognized as numbers.
  • Text with numbers are not recognized as numbers.

Note: You can find the complete documentation for the VBA IsNumeric function here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

How to Convert String to Integer in VBA
How to Convert String to Double in VBA
How to Check if String Contains Another String in VBA

Leave a Reply

Your email address will not be published. Required fields are marked *