How to Use IsNA in VBA (With Examples)

You can use the IsNA method in VBA to check if a given cell contains #N/A or not.

This function will return TRUE if the cell contains #N/A or FALSE otherwise.

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

Sub UseIsNA()

Dim i As Integer

For i = 2 To 10
    Range("B" & i) = WorksheetFunction.IsNA(Range("A" & i))
Next i

End Sub

This particular macro will check if each cell in the range A2:A10 contains #N/A or not.

If a cell contains #N/A, then TRUE will be returned in the corresponding cell in the range B2:B10.

Otherwise, FALSE will be returned.

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

Example: How to Use IsNA in VBA

Suppose we have the following column of values in Excel:

Suppose we would like to check if each cell in column A contains #N/A or not.

We can create the following macro to do so:

Sub UseIsNA()

Dim i As Integer

For i = 2 To 10
    Range("B" & i) = WorksheetFunction.IsNA(Range("A" & i))
Next i

End Sub

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

Column B displays output that tells us whether or not each corresponding cell in column A is equal to #N/A or not.

Note that blank cells are not the same as #N/A. When a blank cell is encountered, the IsNA method returns FALSE.

Also note that you could use an If statement to return values other than TRUE and FALSE.

For example, we could create the following macro instead:

Sub UseIsNA()

Dim i As Integer

For i = 2 To 10
    If WorksheetFunction.IsNA(Range("A" & i)) Then
        Range("B" & i) = "Cell Contains #N/A"
    Else
        Range("B" & i) = "Cell Does Not Contain #N/A"
    End If
Next i

End Sub

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

Column B now contains specific text that tells us whether or not the corresponding cell in column A contains #N/A.

Note: You can find the complete documentation for the VBA IsNA method 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