Trouble using VLookup in VBA

[plh]

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,
-plh

 

[plh]

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

 

[plh]

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?
Thanks,
-plh

 

[plh]

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.
-plh

 

[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"
else
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
sufficient.

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()
and
application.match() and application.worksheetfunction.match()
works.

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.