Cant find VLookup property

M

Merlynsdad

I have a line of code that has been working for weeks in a macro and is as
follows:

strSeid =
Application.WorksheetFunction.VLookup(Worksheets("Temp").Range("$B$31"),
Worksheets("Lists").Range("B2:E250"), 2, False)

All of a sudden this morning when the code gets to it I'm getting an error
"Unable to get the Vlookup property of the Worksheet function class." I have
no idea what's going on. Nothing has changed in this workbook. The ranges are
valid, the variable is Dim'd, the value exists in the Lists range.

Any help will be greatly appreciated!
 
J

Jacob Skaria

The lookup value is missing...Modify your code to handle that

Dim strSeid As Variant
strSeid = Application.VLookup(Worksheets("Temp").Range("$B$31"), _
Worksheets("Lists").Range("B2:E250"), 2, False)
If IsError(strSeid) Then
MsgBox "Cannot find"
Else
MsgBox strSeid
End If

If this post helps click Yes
 
M

Merlynsdad

You're correct; there was a problem with the match. I put your code in, but
if there is no match it goes directly to the error handler for the module and
never gets to the If IsError line. If I put an "OnError Resume Next" in
before the vlookup line, how do I get rid of it after that line so the module
error handler still works?
 
J

Jacob Skaria

Please note that the variable is declared as variant

Dim strSeid As Variant


If this post helps click Yes
 
D

Dave Peterson

And Jacob didn't use application.worksheetfunction.vlookup().

He use application.vlookup().

That syntax doesn't cause a runtime error if there is no match
(application.worksheetfunction.vlookup() will cause a runtime error if there is
no match).
 
C

Charlie

On Error GoTo 0

....will turn off the On Error Resume Next (Note that's a zero not letter "o")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top