Vlookup macro run time error

[Guest]

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(userselection,
classlistrange,2)

Excel 2000.

Hope you can help !

 

[Dave Peterson]

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.

 

[Guest]

Thanks

I had my result as string not variant

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.

 

[Guest]

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]

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.

 

[Guest]

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]

Did you declare ClassListRange as a variable, too?

if yes:

dim ClassListRange as Range
set classlistrange = worksheets("somesheetnamehere").range("classlistrange")
Readerresult = application.vlookup(userselection, Classlistrange,2)

if no:

Readerresult = application.vlookup(userselection, _
worksheets("somesheetnamehere").range("Classlistrange"),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("Classlistrange"),2, 0)

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 !

 

[Guest]

Finally got there; I thought I had properly declared all the variables !

Thanks for all your help

 

[Dave Peterson]

Watch that 4th argument, too.

Finally got there; I thought I had properly declared all the variables !

Thanks for all your help