VLookup in VBA

Hi jackson7249,

I'd say there's no exact match for what's in cell AD6 of the ITEMS tab within the range B3:F65 of the PLANTS tab.

Here's two alternatives (I personally like the Evaluate method):

Option Explicit
Sub Macro1()

    Dim varResult As Variant
    
    On Error Resume Next
        varResult = CVar(Application.WorksheetFunction.VLookup(Sheets("ITEMS").Range("AD6"), Sheets("PLANTS").Range("B3:F65"), 2, False))
    On Error GoTo 0
    
    With Sheets("ITEMS").Range("O12")
        If IsEmpty(varResult) = True Then
            .Value = 0 'Default is zero if the VLOOKUP formula fails. Change to suit.
        Else
            .Value = varResult
        End If
    End With

End Sub
Sub Macro2()

    Sheets("ITEMS").Range("O12").Value = Evaluate("IFERROR(VLOOKUP(ITEMS!AD6,PLANTS!B5:F65,2,FALSE),0)") 'Default is zero if the VLOOKUP formula fails. Change to suit.

End Sub

Regards,

Robert