Trouble using VLookup in VBA



Hi Everyone,
MS Excel 2003 SP3:
VLookup is on the list of functions available listed by Help, but the
folowing code generates the error "Sub or Function not defined".

If IsNA(VLookup(sT, "WasinoParts", 1, True)) = False Then
IsWONPart = True
End If

The blue light is on "VLookup". Seems it is OK with IsNA. I am
thinking I need to load a reference library but can anyone tell me
which one? The list is long and bewildering. The ones I have loaded
are as follows:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Your help would be very much appreciated!
Thank You,


I figured it out:
With Application.WorksheetFunction
If .IsNA(.VLookup(sT, "WasinoParts", 1, True)) = False Then
IsWONPart = True
End If
End With


Actually I am not out of the woods yet. I am getting RT error 1004
"Unable to get the VLookup property of the WorksheetFunction class"
Can anyone help me with this problem?


OK, I'll bite: I was able to solve that one by searchin on this group
and the Internet at large. I'll have to be less quick to post next
time I run into a problem.

Dave Peterson

Even though you found an answer, I like this technique:

Dim res as variant 'could return an error
dim LookUpCol as range

set lookupcol = worksheets("somesheetname").range("A1:A10") 'whatever??

res = application.match(sT, lookupcol, false)

if iserror(res) then
msgbox "not found"
msgbox "Found in row: " & res & " of the lookupcol"
end if

A couple of things...

I changed the function to =match() and I looked for an exact match. Since
you're only looking to see if the value is in the list, I figured =match() was

But changing to look for an exact match may not be what you want! You can look
at Excel's help to see what that third parm (-1, 0, 1) can do.

But the biggest thing I did was to drop the .worksheetfunction from the code.

There's a difference between how
application.vlookup() and application.vlookup()
application.match() and application.worksheetfunction.match()

The first returns an error that can be checked -- that's why I like it.

The second causes a runtime error that has to be avoided with "on error" code.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question