VBA if vlookup value found #N/A in any row then code should be stop

[negi]

Hi All,
I'm trying , if vlookup value found #N/A in any row then code should be stop and display msg with error row number.




Sub nam()
Dim x As Long

x = Range("g" & Rows.Count).End(xlUp).Row


Range("N2:N" & x).FormulaR1C1 = "=VLOOKUP(RC[-7],br_name,3,0)"

End Sub

 

[Joe4]

When you apply that formula to a whole range like that, there is really no way to "stop" when it finds an error since you are doing the whole range at once.
However, we can loop through the range after that and look for errors, i.e.

    Dim r As Long
    For r = 2 To x
        If IsError(Cells(r, "N")) Then
            MsgBox "Error on row " & r
            Exit Sub
        End If
    Next r

If you want to check each one as it is being created, you will need to add the formula assignment into the loop so you are populating one row at a time, i.e.

Sub nam()

    Dim x As Long
    Dim r As Long

    x = Range("g" & Rows.Count).End(xlUp).Row
    
    For r = 2 To x
        Cells(r, "N").FormulaR1C1 = "=VLOOKUP(RC[-7],br_name,3,0)"
        If IsError(Cells(r, "N")) Then
            MsgBox "Error on row " & r
            Exit Sub
        End If
    Next r

End Sub

Note that using loops may slow your code down if you have a lot of rows you are looping through, but I think that is the price you have to pay if you want it to return the error and stop at that line of code without finishing the rest.