Cant find VLookup property

[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!

 

[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

 

[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?

 

[Jacob Skaria]

Please note that the variable is declared as variant

Dim strSeid As Variant


If this post helps click Yes

 

[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).

 

[Charlie]

On Error GoTo 0

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