VBA Syntax to Lookup Value in a Table

Using:  Excel 2010

I'm trying to code a VBA proc to lookup a filename from the 2nd column in an Excel Table (ideally I would prefer to look this value up based on the Header Label and not based on an index, in case columns are subsequently added), based on a lookup value which corresponds to the value in the 1st column.

Referring to the image below, i'm trying to do something like:

lookupval = "007"
fileNm = Application.WorksheetFunction.VLookup(lookupval, Sheet1.Range("Table1"), 2, False)

where I'm assuming that the third argument in VLookup is an offset from the lookupval column, which would cause the value under column "Filename" to be assigned to 'fileNm'

In my example, given lookupval = "007", I would want to retrieve fileNm = "someotherfilename.pdf" and then open that file in Acrobat Reader.

I'm not clear on the syntax.  I presently get the following MS Visual Basic run-time error '1004':

"Unable to get the VLookup property of the function Worksheet class"

Your help is appreciated.

VBA-Syntax-for-Table-Lookup.png
qengAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

qengAuthor Commented:
OK, I'll add some more info:

I was able to get the following code to run error free in a test workbook, but I will post in the next comment the code in a different workbook which results in an error despite seemingly using the same syntax:

This code retrieves the correct filename with the table shown in the first post above:

Sub GetFilename()
    Dim lookupval As Variant
    Dim fileNm As String
   
    lookupval = 7
'
    MsgBox ("Index# of filename to get is ") & lookupval
'
    fileNm = Application.WorksheetFunction.VLookup(lookupval, Sheet1.Range("Table1"), 2, False)
    MsgBox ("Filename to open is '") & fileNm & "'"
End Sub
0
Saqib Husain, SyedEngineerCommented:
Try

Sub GetFilename()
    Dim lookupval As Variant
    Dim fileNm As String
   
    lookupval = 7
    colname = "Filename"
'
    MsgBox ("Index# of filename to get is ") & lookupval
'
    colno = WorksheetFunction.Match(colname, Sheet1.Range("Table1").Resize(1).Offset(-1), 0)
    fileNm = Application.WorksheetFunction.VLookup(lookupval, Sheet1.Range("Table1"), colno, False)
    MsgBox ("Filename to open is '") & fileNm & "'"
    tables
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Harry LeeCommented:
Qeng,

You can use this for your example

Option Explicit
Sub LOOKUPTEST()
Dim WS As Worksheet, Tbl As ListObject, ColHeader As String, ColNum As Long, LookupVal As Variant

LookupVal = "007"

Set WS = Sheet1
Set Tbl = WS.ListObjects("Table1")
ColHeader = "Filename"

ColNum = Application.WorksheetFunction.Match(ColHeader, Tbl.HeaderRowRange, 0)
MsgBox (Application.WorksheetFunction.Index(Tbl.ListColumns(ColNum).DataBodyRange, Application.WorksheetFunction.Match(LookupVal, Tbl.ListColumns(1).DataBodyRange)))


End Sub

Open in new window

You can configure the variables LookupVal, WS, Tbl, and ColHeader for your need.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.