VLOOKUP error in VBA

[brett.kaplan]

Hi,

I am doing some VLookups in visual basic and am running into an issue.
Basically, I am running 7 vlookups on the same value in different
columns. If the vlookup does not return an error, I want to report the
number in column 3 of the range; if it is an error (ie, the lookup
value does not exist in the range), I want to report a 0.

When the lookup value exists, my formula works fine. However, when it
does not exist, the macro always ends with a run-time error. I tried
to get around this using:

If IsError(Application.WorksheetFunction.VLookup(Arg1, Arg2, 3, False))
= True Then
myVar = 0
Else
myVar = Application.WorksheetFunction.VLookup(Arg1, Arg2, 3, False)
End If

However, it still stops with a runtime error on the If IsError line.

How can I work around this?

Thanks!
Brett

 

[Dave Peterson]

Drop the .worksheetfunction from the line.

If IsError(Application.VLookup(Arg1, Arg2, 3, False)) = True Then

But instead of (maybe) doing it twice, just do it once:

dim myVar as Variant
myVar = application.vlookup(arg1, arg2, 3, false)
if iserror(myVar) then
myvar = 0
end if