Revision history [back]

One way to identify the row that contains the formula result #N/A in Excel VBA is to loop through the cells in the desired range and use the IsError function to check if the cell contains an error. If the cell contains an error, check if the error is equal to #N/A using the IsNA function. If the cell contains #N/A, store the row number in a variable. Here is an example code snippet:

Dim ws As Worksheet
Dim lastRow As Long
Dim errorRow As Long

Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
errorRow = 0

For i = 1 To lastRow
    If IsError(ws.Cells(i, "A").Value) Then
        If IsNA(ws.Cells(i, "A").Value) Then
            errorRow = i
            Exit For
        End If
    End If
Next i

If errorRow <> 0 Then
    MsgBox "The row containing #N/A is " & errorRow
Else
    MsgBox "No rows contain #N/A"
End If

In this example, the code checks for #N/A in column A of "Sheet1". The last row of data in the column is determined by finding the last non-blank cell. The variable errorRow is initially set to 0, and if a row containing #N/A is found, the row number is stored in this variable and the loop is exited using the Exit For statement. Finally, a message box is displayed indicating the row number containing #N/A or indicating that no rows contain #N/A.