How to apply vlookup with on error value 0 and resume

Question

I am using vlookup range to apply formula in last empty column. its working fine but if error is there it's showing blank cell, Well,I dont mind that but, when the formula is applied again in next column the value is shifted to previous column's blank cell, as one of the cell in previous column was blank due to error.

So I need it to give value 0 or "error" instead of blank.

Sub GET_BHAV()

    Dim OpenWs As Worksheet, bhavWs As Worksheet
    Dim OpenLastRow As Long, bhavLastRow As Long, x As Long
    Dim bhavRng As Range

    Set OpenWs = ThisWorkbook.Worksheets("Open")

    Workbooks.Open "C:\Users\playt\Desktop\STACK\VANGU\cm07JAN2020bhav.csv"

    Set bhavWs = Workbooks("cm07JAN2020bhav.csv").Worksheets("cm07JAN2020bhav")

    bhavLastRow = bhavWs.Range("A" & Rows.Count).End(xlUp).Row
    OpenLastRow = OpenWs.Range("A" & Rows.Count).End(xlUp).Row

    Set bhavRng = bhavWs.Range("A2:G" & bhavLastRow)

    For x = 2 To OpenLastRow
        On Error Resume Next
        OpenWs.Cells(x, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Application.WorksheetFunction.VLookup( _
                                                                          OpenWs.Range("A" & x).Value, bhavRng, 3, Flase)
    Next x

End Sub

Answer 1

You need to trap (handle) the VLOOKUP error, in case it doesn't find a match.

Try the code below of the VLOOKUP section:

' define a new variable
Dim VlookUpVal As Variant

With OpenWs ' Using With statement
    For x = 2 To OpenLastRow
        ' read the value of VLOOKUP to variant
        VlookUpVal = Application.VLookup(.Range("A" & x).Value, bhavRng, 3, False)

        ' check if VLOOKUP returned an Error
        If IsError(VlookUpVal) Then
            .Cells(x, .Columns.Count).End(xlToLeft).Offset(0, 1).Value = 0
        Else
            .Cells(x, .Columns.Count).End(xlToLeft).Offset(0, 1).Value = VlookUpVal
        End If
    Next x
End With