Variable Issue

[Mike H.]

I have a variable assigned to Variant type and I have this statement before
The variable is assigned:
On Error Goto XXXX
THe variable gets its value as follows:
Res = Application.VLookup(Cells(X, 1) & Cells(X, 41), LookupRng, 3,
False)
The result is error 42, but the application does not goto XXXX but instead I
get an error 42, runtime error 13, type mismatch. I am not sure what to do I
can't seem to get the program to just skip over the assignment. Suggestions?

 

[Dave Peterson]

Drop the "on error goto xxxx" and just check for an error:

dim Res as variant
....
res = application.vlookup(...)
if iserror(res) then
'you got an error
else
'it wasn't an error
end if

Or

You could use
Dim res as variant
on error goto xxxx:
res = application.worksheetfunction.vlookup(...)
on error goto 0

....
exit sub

xxxx:
msgbox "found an error"

=======
There's a difference between the way application.vlookup() and
application.worksheetfunction.vlookup() works. (same with application.match and
application.worksheetfunction.match, fyi.)

 

[Mike H.]

That solved my problem!