Dim myVal as Variant 'long, string, ???
Dim myRng as range
dim res as variant 'could return an error

with worksheets("Sheet2")
set myrng = .range("a:e") 'some range
end with

myval = worksheets("Sheet1").range("A1").value

res = application.vlookup(myval, myrng, 5, false)
if iserror(res) then
msgbox "No match" 'like #N/A in excel
else
msgbox res
end if

=========
There is a difference in the way application.vlookup() and
worksheetfunction.vlookup() (or application.worksheetfunction.vlookup()) behave.

Saved from a previous post:

Application.vlookup returns an error that you can check:

dim Res as variant 'could return an error
res = application.vlookup(....)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

Application.worksheetfunction.vlookup raises a trappable error that you have to
catch:

dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number < 0 then
msgbox "no match"
else
msgbox res
end if
on error goto 0

(application.match() and application.worksheetfunction.match() behave the same
way.)

Personally, I find using the application.vlookup() syntax easier to read. But
it's personal preference.


hverne wrote:

How do I do the equivalent of the Excel VLOOKUP function in an Excel (2007)
Macro?

--

Dave Peterson