Search code examples
exceltextvlookupselectedvba

Vlookup Macro Using Manually Selected Cell

I'm just a beginner, but I have a simple Vlookup macro that I can't get to work.

Imagine an excel sheet that has a column with different numbers in each row. After a number is selected, the macro shortcut is used and a vlookup should run and return the text associated with that number.

Dim Resource As String
Resource = Selection.Copy
rName = Application.WorksheetFunction.VLookup(Resource, Sheets("Program Title").Range("D5:F305"), 3,   False)
MsgBox "" & rName

End Sub

When I run this code, I get an "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class"

Can someone let me know how this code should be fixed?

Solution 
  • Dim Resource As String, rName As Variant
Resource = Selection.Value

rName = Application.VLookup(Resource, Sheets("Program Title").Range("D5:F305"), _
                            3, False)

If Not IsError(rName) Then
    MsgBox "" & rName
Else
    MsgBox Resource & " not found in lookup table"
End If