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.
Asked: 2023-05-05 00:20:44 +0000
Seen: 15 times
Last updated: May 05 '23