Programming around Vlookup Errors in VBA
 

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

Programming around Vlookup Errors in VBA
 

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
on error goto 0




Elceller in distress wrote:

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

Programming around Vlookup Errors in VBA
 

Thanks, It worked!

"Dave Peterson" wrote:

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
on error goto 0




Elceller in distress wrote:

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