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

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().




Dan wrote:

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

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

--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

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().




Dan wrote:

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

Yep.

Thanks for the addition.

Jim Thomlinson wrote:

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

--
HTH...

Jim Thomlinson

"Dave Peterson" wrote:

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().




Dan wrote:

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


--

Dave Peterson

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

"Dan" wrote:

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