Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in VBA
when trying to use vlooku in vba
Application.WorksheetFunction.VLookup( ...) I am getting the following vba error Run-time error 1004 Unable to get the Vlookup property of the worksheetFunction class. Any idea what I am missing? Thanks Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in VBA
application.worksheetfunction.vlookup() will cause a runtime error if there is
no match. You can trap that error: dim res as variant 'long, string??? on error resume next res = application.worksheetfunction.vlookup(...) if err.number < 0 then 'an error occurred err.clear res = "not found" end if msgbox res ==== But application.vlookup() will return an error you can check for: I'd use something like: dim Res as variant res = application.vlookup(...) if iserror(res) then res = "not found" end if msgbox res I find the application.vlookup() easier to use. And the same thing for application.match() v. application.worksheetfunction.match(). Dan wrote: when trying to use vlooku in vba Application.WorksheetFunction.VLookup( ...) I am getting the following vba error Run-time error 1004 Unable to get the Vlookup property of the worksheetFunction class. Any idea what I am missing? Thanks Dan -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in VBA
Not to pick but you should probably switch the error handler back to normal
mode at some point... dim res as variant 'long, string??? on error resume next res = application.worksheetfunction.vlookup(...) if err.number < 0 then 'an error occurred err.clear res = "not found" end if On Error Goto 0 msgbox res -- HTH... Jim Thomlinson "Dave Peterson" wrote: application.worksheetfunction.vlookup() will cause a runtime error if there is no match. You can trap that error: dim res as variant 'long, string??? on error resume next res = application.worksheetfunction.vlookup(...) if err.number < 0 then 'an error occurred err.clear res = "not found" end if msgbox res ==== But application.vlookup() will return an error you can check for: I'd use something like: dim Res as variant res = application.vlookup(...) if iserror(res) then res = "not found" end if msgbox res I find the application.vlookup() easier to use. And the same thing for application.match() v. application.worksheetfunction.match(). Dan wrote: when trying to use vlooku in vba Application.WorksheetFunction.VLookup( ...) I am getting the following vba error Run-time error 1004 Unable to get the Vlookup property of the worksheetFunction class. Any idea what I am missing? Thanks Dan -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in VBA
Are capturing the result someplace?
if you don't have something like: res=Application.WorksheetFunction.VLookup( ...) then it might be trying to use it as a property not a method "Dan" wrote: when trying to use vlooku in vba Application.WorksheetFunction.VLookup( ...) I am getting the following vba error Run-time error 1004 Unable to get the Vlookup property of the worksheetFunction class. Any idea what I am missing? Thanks Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in VBA
Yep.
Thanks for the addition. Jim Thomlinson wrote: Not to pick but you should probably switch the error handler back to normal mode at some point... dim res as variant 'long, string??? on error resume next res = application.worksheetfunction.vlookup(...) if err.number < 0 then 'an error occurred err.clear res = "not found" end if On Error Goto 0 msgbox res -- HTH... Jim Thomlinson "Dave Peterson" wrote: application.worksheetfunction.vlookup() will cause a runtime error if there is no match. You can trap that error: dim res as variant 'long, string??? on error resume next res = application.worksheetfunction.vlookup(...) if err.number < 0 then 'an error occurred err.clear res = "not found" end if msgbox res ==== But application.vlookup() will return an error you can check for: I'd use something like: dim Res as variant res = application.vlookup(...) if iserror(res) then res = "not found" end if msgbox res I find the application.vlookup() easier to use. And the same thing for application.match() v. application.worksheetfunction.match(). Dan wrote: when trying to use vlooku in vba Application.WorksheetFunction.VLookup( ...) I am getting the following vba error Run-time error 1004 Unable to get the Vlookup property of the worksheetFunction class. Any idea what I am missing? Thanks Dan -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |