problem with Vlookup in macro


G

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.
 
Ad

Advertisements

Ad

Advertisements

D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top