Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro run time error
I get Run time error 1004 Unable to get the Vlookup property of the worksheet
function class as an error message when i run the following macro command Readerresult = application.worksheetfunction.vlookup(userselectio n, classlistrange,2) Excel 2000. Hope you can help ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro run time error
Try dropping the .worksheetfunction. from the line:
dim ReaderResult as Variant 'note that it's a variant readerresult = application.vlookup(....) if iserror(readerresult) then 'it wasn't found else 'it was found end if If you use the .worksheetfunction stuff, you'll have to trap for the error. dim ReaderResult as String 'long, whatever on error resume next readerresult = application.worksheetfunction.vlookup(....) if err.number < 0 then err.clear 'not found else 'found end if on error goto 0 I find the first method easier to use. Daveh wrote: I get Run time error 1004 Unable to get the Vlookup property of the worksheet function class as an error message when i run the following macro command Readerresult = application.worksheetfunction.vlookup(userselectio n, classlistrange,2) Excel 2000. Hope you can help ! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro run time error
Thanks
I had my result as string not variant "Dave Peterson" wrote: Try dropping the .worksheetfunction. from the line: dim ReaderResult as Variant 'note that it's a variant readerresult = application.vlookup(....) if iserror(readerresult) then 'it wasn't found else 'it was found end if If you use the .worksheetfunction stuff, you'll have to trap for the error. dim ReaderResult as String 'long, whatever on error resume next readerresult = application.worksheetfunction.vlookup(....) if err.number < 0 then err.clear 'not found else 'found end if on error goto 0 I find the first method easier to use. Daveh wrote: I get Run time error 1004 Unable to get the Vlookup property of the worksheet function class as an error message when i run the following macro command Readerresult = application.worksheetfunction.vlookup(userselectio n, classlistrange,2) Excel 2000. Hope you can help ! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro run time error
Dave
earlier optimism unfounded !! The code line now works (berfore it was failing) but I cannot use the vlookup response. I am expecting a "yes" or "no" result from vlookup in my "readerresult" variable but subsequent macro commands do not like whatever the contents of "readerresult" is. I have tried the vlookup function with my data in a spreadsheet and it does what I expect but not when I try to code it in a macro. "Dave Peterson" wrote: Try dropping the .worksheetfunction. from the line: dim ReaderResult as Variant 'note that it's a variant readerresult = application.vlookup(....) if iserror(readerresult) then 'it wasn't found else 'it was found end if If you use the .worksheetfunction stuff, you'll have to trap for the error. dim ReaderResult as String 'long, whatever on error resume next readerresult = application.worksheetfunction.vlookup(....) if err.number < 0 then err.clear 'not found else 'found end if on error goto 0 I find the first method easier to use. Daveh wrote: I get Run time error 1004 Unable to get the Vlookup property of the worksheet function class as an error message when i run the following macro command Readerresult = application.worksheetfunction.vlookup(userselectio n, classlistrange,2) Excel 2000. Hope you can help ! -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro run time error
It sounds like the =vlookup() is returning an error.
if iserror(readerresult) then msgbox "no match found" else msgbox readerresult end if may help your debugging. You may want to post the formula that worked in the cell. And what is contained in the variables in that line of code. Daveh wrote: Dave earlier optimism unfounded !! The code line now works (berfore it was failing) but I cannot use the vlookup response. I am expecting a "yes" or "no" result from vlookup in my "readerresult" variable but subsequent macro commands do not like whatever the contents of "readerresult" is. I have tried the vlookup function with my data in a spreadsheet and it does what I expect but not when I try to code it in a macro. "Dave Peterson" wrote: Try dropping the .worksheetfunction. from the line: dim ReaderResult as Variant 'note that it's a variant readerresult = application.vlookup(....) if iserror(readerresult) then 'it wasn't found else 'it was found end if If you use the .worksheetfunction stuff, you'll have to trap for the error. dim ReaderResult as String 'long, whatever on error resume next readerresult = application.worksheetfunction.vlookup(....) if err.number < 0 then err.clear 'not found else 'found end if on error goto 0 I find the first method easier to use. Daveh wrote: I get Run time error 1004 Unable to get the Vlookup property of the worksheet function class as an error message when i run the following macro command Readerresult = application.worksheetfunction.vlookup(userselectio n, classlistrange,2) Excel 2000. Hope you can help ! -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro run time error
Vlookup is returing an error.
My data table is very simple with 2 columns Name Reader required ? Bill yes Carl no Dave yes etc.. This data table is called Classlistrange The spreadsheet expression does as I expect; for example =Vlookup("Bill",Classlistrange,2) returns yes The VB code fails the iserror(Readerresult) test Readerresult = application.vlookup(userselection, Classlistrange,2) where userselection contains the string "Bill" Hope you can help with this ! "Dave Peterson" wrote: It sounds like the =vlookup() is returning an error. if iserror(readerresult) then msgbox "no match found" else msgbox readerresult end if may help your debugging. You may want to post the formula that worked in the cell. And what is contained in the variables in that line of code. Daveh wrote: Dave earlier optimism unfounded !! The code line now works (berfore it was failing) but I cannot use the vlookup response. I am expecting a "yes" or "no" result from vlookup in my "readerresult" variable but subsequent macro commands do not like whatever the contents of "readerresult" is. I have tried the vlookup function with my data in a spreadsheet and it does what I expect but not when I try to code it in a macro. "Dave Peterson" wrote: Try dropping the .worksheetfunction. from the line: dim ReaderResult as Variant 'note that it's a variant readerresult = application.vlookup(....) if iserror(readerresult) then 'it wasn't found else 'it was found end if If you use the .worksheetfunction stuff, you'll have to trap for the error. dim ReaderResult as String 'long, whatever on error resume next readerresult = application.worksheetfunction.vlookup(....) if err.number < 0 then err.clear 'not found else 'found end if on error goto 0 I find the first method easier to use. Daveh wrote: I get Run time error 1004 Unable to get the Vlookup property of the worksheet function class as an error message when i run the following macro command Readerresult = application.worksheetfunction.vlookup(userselectio n, classlistrange,2) Excel 2000. Hope you can help ! -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro run time error
Did you declare ClassListRange as a variable, too?
if yes: dim ClassListRange as Range set classlistrange = worksheets("somesheetnamehere").range("classlistra nge") Readerresult = application.vlookup(userselection, Classlistrange,2) if no: Readerresult = application.vlookup(userselection, _ worksheets("somesheetnamehere").range("Classlistra nge"),2) And in either case, since you're matching on text values (names), I would think you'd want an exact match. Your formula should have 0 or False as the 4th argument. Readerresult = application.vlookup(userselection, Classlistrange,2, false) or Readerresult = application.vlookup(userselection, _ worksheets("somesheetnamehere").range("Classlistra nge"),2, 0) Daveh wrote: Vlookup is returing an error. My data table is very simple with 2 columns Name Reader required ? Bill yes Carl no Dave yes etc.. This data table is called Classlistrange The spreadsheet expression does as I expect; for example =Vlookup("Bill",Classlistrange,2) returns yes The VB code fails the iserror(Readerresult) test Readerresult = application.vlookup(userselection, Classlistrange,2) where userselection contains the string "Bill" Hope you can help with this ! "Dave Peterson" wrote: It sounds like the =vlookup() is returning an error. if iserror(readerresult) then msgbox "no match found" else msgbox readerresult end if may help your debugging. You may want to post the formula that worked in the cell. And what is contained in the variables in that line of code. Daveh wrote: Dave earlier optimism unfounded !! The code line now works (berfore it was failing) but I cannot use the vlookup response. I am expecting a "yes" or "no" result from vlookup in my "readerresult" variable but subsequent macro commands do not like whatever the contents of "readerresult" is. I have tried the vlookup function with my data in a spreadsheet and it does what I expect but not when I try to code it in a macro. "Dave Peterson" wrote: Try dropping the .worksheetfunction. from the line: dim ReaderResult as Variant 'note that it's a variant readerresult = application.vlookup(....) if iserror(readerresult) then 'it wasn't found else 'it was found end if If you use the .worksheetfunction stuff, you'll have to trap for the error. dim ReaderResult as String 'long, whatever on error resume next readerresult = application.worksheetfunction.vlookup(....) if err.number < 0 then err.clear 'not found else 'found end if on error goto 0 I find the first method easier to use. Daveh wrote: I get Run time error 1004 Unable to get the Vlookup property of the worksheet function class as an error message when i run the following macro command Readerresult = application.worksheetfunction.vlookup(userselectio n, classlistrange,2) Excel 2000. Hope you can help ! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro run time error
Finally got there; I thought I had properly declared all the variables !
Thanks for all your help "Dave Peterson" wrote: Did you declare ClassListRange as a variable, too? if yes: dim ClassListRange as Range set classlistrange = worksheets("somesheetnamehere").range("classlistra nge") Readerresult = application.vlookup(userselection, Classlistrange,2) if no: Readerresult = application.vlookup(userselection, _ worksheets("somesheetnamehere").range("Classlistra nge"),2) And in either case, since you're matching on text values (names), I would think you'd want an exact match. Your formula should have 0 or False as the 4th argument. Readerresult = application.vlookup(userselection, Classlistrange,2, false) or Readerresult = application.vlookup(userselection, _ worksheets("somesheetnamehere").range("Classlistra nge"),2, 0) Daveh wrote: Vlookup is returing an error. My data table is very simple with 2 columns Name Reader required ? Bill yes Carl no Dave yes etc.. This data table is called Classlistrange The spreadsheet expression does as I expect; for example =Vlookup("Bill",Classlistrange,2) returns yes The VB code fails the iserror(Readerresult) test Readerresult = application.vlookup(userselection, Classlistrange,2) where userselection contains the string "Bill" Hope you can help with this ! "Dave Peterson" wrote: It sounds like the =vlookup() is returning an error. if iserror(readerresult) then msgbox "no match found" else msgbox readerresult end if may help your debugging. You may want to post the formula that worked in the cell. And what is contained in the variables in that line of code. Daveh wrote: Dave earlier optimism unfounded !! The code line now works (berfore it was failing) but I cannot use the vlookup response. I am expecting a "yes" or "no" result from vlookup in my "readerresult" variable but subsequent macro commands do not like whatever the contents of "readerresult" is. I have tried the vlookup function with my data in a spreadsheet and it does what I expect but not when I try to code it in a macro. "Dave Peterson" wrote: Try dropping the .worksheetfunction. from the line: dim ReaderResult as Variant 'note that it's a variant readerresult = application.vlookup(....) if iserror(readerresult) then 'it wasn't found else 'it was found end if If you use the .worksheetfunction stuff, you'll have to trap for the error. dim ReaderResult as String 'long, whatever on error resume next readerresult = application.worksheetfunction.vlookup(....) if err.number < 0 then err.clear 'not found else 'found end if on error goto 0 I find the first method easier to use. Daveh wrote: I get Run time error 1004 Unable to get the Vlookup property of the worksheet function class as an error message when i run the following macro command Readerresult = application.worksheetfunction.vlookup(userselectio n, classlistrange,2) Excel 2000. Hope you can help ! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro run time error
Watch that 4th argument, too.
Daveh wrote: Finally got there; I thought I had properly declared all the variables ! Thanks for all your help "Dave Peterson" wrote: Did you declare ClassListRange as a variable, too? if yes: dim ClassListRange as Range set classlistrange = worksheets("somesheetnamehere").range("classlistra nge") Readerresult = application.vlookup(userselection, Classlistrange,2) if no: Readerresult = application.vlookup(userselection, _ worksheets("somesheetnamehere").range("Classlistra nge"),2) And in either case, since you're matching on text values (names), I would think you'd want an exact match. Your formula should have 0 or False as the 4th argument. Readerresult = application.vlookup(userselection, Classlistrange,2, false) or Readerresult = application.vlookup(userselection, _ worksheets("somesheetnamehere").range("Classlistra nge"),2, 0) Daveh wrote: Vlookup is returing an error. My data table is very simple with 2 columns Name Reader required ? Bill yes Carl no Dave yes etc.. This data table is called Classlistrange The spreadsheet expression does as I expect; for example =Vlookup("Bill",Classlistrange,2) returns yes The VB code fails the iserror(Readerresult) test Readerresult = application.vlookup(userselection, Classlistrange,2) where userselection contains the string "Bill" Hope you can help with this ! "Dave Peterson" wrote: It sounds like the =vlookup() is returning an error. if iserror(readerresult) then msgbox "no match found" else msgbox readerresult end if may help your debugging. You may want to post the formula that worked in the cell. And what is contained in the variables in that line of code. Daveh wrote: Dave earlier optimism unfounded !! The code line now works (berfore it was failing) but I cannot use the vlookup response. I am expecting a "yes" or "no" result from vlookup in my "readerresult" variable but subsequent macro commands do not like whatever the contents of "readerresult" is. I have tried the vlookup function with my data in a spreadsheet and it does what I expect but not when I try to code it in a macro. "Dave Peterson" wrote: Try dropping the .worksheetfunction. from the line: dim ReaderResult as Variant 'note that it's a variant readerresult = application.vlookup(....) if iserror(readerresult) then 'it wasn't found else 'it was found end if If you use the .worksheetfunction stuff, you'll have to trap for the error. dim ReaderResult as String 'long, whatever on error resume next readerresult = application.worksheetfunction.vlookup(....) if err.number < 0 then err.clear 'not found else 'found end if on error goto 0 I find the first method easier to use. Daveh wrote: I get Run time error 1004 Unable to get the Vlookup property of the worksheet function class as an error message when i run the following macro command Readerresult = application.worksheetfunction.vlookup(userselectio n, classlistrange,2) Excel 2000. Hope you can help ! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
First Time Error Using VLOOKUP | Excel Discussion (Misc queries) | |||
Run Time Error for Vlookup & Match command | Excel Programming | |||
VBA VLookup Problem: Run-Time error '1004' | Excel Programming |