Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup errors #n/a | Excel Worksheet Functions | |||
VLOOKUP AND N/A ERRORS | Excel Discussion (Misc queries) | |||
Programming around vlookup errors in vba | Excel Programming | |||
Vlookup errors | Excel Programming | |||
Need Help with Programming-Syntax/Compile Errors | Excel Programming |