VBA Lookup Error - tried researching here and cannot find a working answer

user_8015242 0
Viewed 41

Newbie here trying to find code examples online. I tried searching for this answer, but couldn't find something that worked -

Trying to use lookup to pull specific values from a table from a different sheet.

Sheet6 (Test) has:

A2:A5 = Red, Green, Blue, Yellow.
B2:B5 = Apple, Banana, Berry, Lemon

A9 (The lookup value) = Blue

and I'd like VBA to enter "Berry" into B9.

The code I tried is:

Sub VBA_Lookup1()
   Range("B9").value = worksheetfunction.lookup(Range("A9").value,Range("A2:A5"),Range("B2:B5"))
End Sub

When run I get the error message "unable to get the lookup property of the worksheetfunction class"

Ultimately, I'd like the lookup value to be in one sheet, and the table to be in another, but I can't even get it to work in test.

Also, if there's a find or other function that would work better than lookup, I'm all ears!

lookup vba excel
2 Answers

Try (assuming the active sheet is where A9 is located:

With Worksheets("Sheet6")
   Range("B9").Value = WorksheetFunction.VLookup(Range("A9").Value, .Range("A2:B5"), 2, 0)
End With
user_28729220

Match (Application.Match)

Excel

=IFERROR(INDEX(Sheet6!$B$2:$B$5,MATCH(A9,Sheet6!$A$2:$A$5,0)),"")

VBA

Option Explicit

Sub Test()

    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook ' Workbook containing this code
    
    ' Worksheets
    Dim src As Worksheet: Set src = wb.Worksheets("Sheet6")
    Dim dst As Worksheet: Set dst = wb.Worksheets("Sheet1") ' change to...
    
    ' Atttempt to find a match.
    Dim cMatch As Variant ' 'Variant', because it could be an error value.
    cMatch = Application.Match(dst.Range("A9").Value, src.Range("A2:A5"), 0)
    
    ' Test the result of the match.
    If IsNumeric(cMatch) Then
        ' Write result (cMatch is a number).
        dst.Range("B9").Value = src.Range("B2:B5").Cells(cMatch).Value
    Else
        ' Write result (cMatch is an error value).
        dst.Range("B9").Value = Empty ' or '= ""' or '= 0'
        MsgBox "Value not found."
    End If

End Sub
user_98140690
Related