Programming around Vlookup Errors in VBA

[Guest]

I have a program that uses vlookup to find values that are stored in a quote.
Some of the older quotes do no contain all of the fields “Lookup_Valuesâ€
that are in the newer quotes. When the program connot find a fileld
“Lookup_Value", excel stops my macro stops and highlightsts the vlookup
function that I am using. Is there a way to use if(iserror(vlookup))then...
or any other functions to work around this problem? I would like the vlookup
function to return "" to the active cell if the field "Lookup value" does not
exist.


Dim SerialRange As Range 'The range of the serial field, from the quote
Set SerialRange = Worksheets("Sheet1").Range("A:C")
Serial = (Application.WorksheetFunction.Vlookup("SER #:", SerialRange,
3, False))
ActiveCell.Formula = Serial

 

[Dave Peterson]

You have a couple of choices...

I like using application.vlookup() instead of
application.worksheetfunction.vlookup().

Dim Serial as Variant 'could be an error

serial = application.vlookup("ser #:", serialrange,3,false)

if iserror(searial) then
'no match code here
else
'found it code here
end if

====
or

on error resume next
serial = application.worksheetfunction.vlookup("ser #:", serialrange,3,false)
if err.number <> 0 then
'no match code here
else
'found it code here
end if

 

[Guest]

Thanks, It worked!

You have a couple of choices...

I like using application.vlookup() instead of
application.worksheetfunction.vlookup().

Dim Serial as Variant 'could be an error

serial = application.vlookup("ser #:", serialrange,3,false)

if iserror(searial) then
'no match code here
else
'found it code here
end if

====
or

on error resume next
serial = application.worksheetfunction.vlookup("ser #:", serialrange,3,false)
if err.number <> 0 then
'no match code here
else
'found it code here
end if