You can use the IsError function in VBA to check if a given cell contains an error value and return TRUE or FALSE as a result.
Here is one common way to use this function in practice:
Sub CheckIsError()
Dim i As Integer
For i = 2 To 11
Range("B" & i).Value = WorksheetFunction.IsError(Range("A" & i))
Next i
End Sub
This particular macro checks if each cell in the range A2:A11 is an error value and returns either TRUE or FALSE in the corresponding cell in the range B2:B11.
The following example shows how to use this syntax in practice.
Example: How to Use IsError Function in VBA
Suppose we have the following column of values in Excel:
Suppose we would like to check if each value in column A is an error value or not.
We can create the following macro to do so:
Sub CheckIsError()
Dim i As Integer
For i = 2 To 11
Range("B" & i).Value = WorksheetFunction.IsError(Range("A" & i))
Next i
End Sub
When we run this macro, we receive the following output:
The values in column B display either TRUE or FALSE to indicate whether or not the corresponding values in column A are error values.
Note that the following values all return TRUE in column B:
- #DIV/0!
- #VALUE!
- #NUM!
All other values return FALSE since they are not error values.
Also note that the empty cell A10 does not return TRUE because it is not an error value – it is simply empty.
Note: You can find the complete documentation for the VBA IsError function here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Use IF OR to Test Multiple Conditions
VBA: How to Use IF AND to Test Multiple Conditions
VBA: How to Use IF NOT to Test if Condition is Not Met
