problem with Vlookup in macro

[Guest]

I need a Vlookup program for the general case where there may not be data in
the Vlookup lookup table - i.e. Vlookup returns the NA condition.

Normally I handle this in a worksheet function using the IsNA function.
However, I can't do the same think in a macro program - e.g. I get an error
with the following command when Vlookup returns the NA condition

test =
Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(lookupvalue, lookuprange, 2, False))

I've also tried the programming error functions IsMissing and IsNull.

 

[Ardus Petus]

Use error trapping:
on error goto Lokkup_failed

HTH

 

[Dave Peterson]

I like to use application.vlookup().

dim res as variant
res = application.vlookup(...)

if iserror(res) then
'not found
else
'it was found
end if

application.worksheetfunction.vlookup causes a trappable error.

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