Vlookup in VBA

[Guest]

when trying to use vlooku in vba
Application.WorksheetFunction.VLookup( ...)
I am getting the following vba error
Run-time error 1004
Unable to get the Vlookup property of the worksheetFunction class.

Any idea what I am missing?
Thanks
Dan

 

[Dave Peterson]

application.worksheetfunction.vlookup() will cause a runtime error if there is
no match.

You can trap that error:

dim res as variant 'long, string???
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number <> 0 then
'an error occurred
err.clear
res = "not found"
end if
msgbox res

====

But application.vlookup() will return an error you can check for:

I'd use something like:

dim Res as variant
res = application.vlookup(...)
if iserror(res) then
res = "not found"
end if
msgbox res

I find the application.vlookup() easier to use.

And the same thing for application.match() v.
application.worksheetfunction.match().

 

[Guest]

Not to pick but you should probably switch the error handler back to normal
mode at some point...

dim res as variant 'long, string???
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number <> 0 then
'an error occurred
err.clear
res = "not found"
end if
On Error Goto 0
msgbox res

 

[Guest]

Are capturing the result someplace?
if you don't have something like:
res=Application.WorksheetFunction.VLookup( ...)

then it might be trying to use it as a property not a method

 

[Dave Peterson]

Yep.

Thanks for the addition.

Not to pick but you should probably switch the error handler back to normal
mode at some point...

dim res as variant 'long, string???
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number <> 0 then
'an error occurred
err.clear
res = "not found"
end if
On Error Goto 0
msgbox res