VLOOKUP & ISNA

[wpiet]

"If .WorksheetFunction.IsNA _
(.WorksheetFunction.VLookup(CopyVal, LookupRng, 1, False)) Then "

If CopyVal does not exist in the leftmost column of LookupRng, I get
"Run-time error '1004': Unable to get the VLookup property of the
WorksheetFunction class."

If CopyVal is a value that exists in LookupRng, the code gets past the If
statement, since the value of the VLookup is not = #N/A.

Excel 2003 Help for function VLOOKUP states, in re: Range_lookup, "If FALSE,
VLOOKUP will find an exact match. If one is not found, the error value #N/A
is returned."

Why, then, does the VLookup fail, rather than return #N/A, if the value is
not there?

 

[Dave Peterson]

Saved from a previous post:

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

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.

 

[wpiet]

Well, that was easy!
Thank you, Dave.
Obviously, I missed that previous post in my research.
Thanks for the excellent insights on the differences.