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!

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
---------------
Jacob Skaria


"Merlynsdad" wrote:

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

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" wrote:

> 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
> ---------------
> Jacob Skaria
>
>
> "Merlynsdad" wrote:
>
> > 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!

Please note that the variable is declared as variant

Dim strSeid As Variant


If this post helps click Yes
---------------
Jacob Skaria


"Merlynsdad" wrote:

> 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" wrote:
>
> > 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
> > ---------------
> > Jacob Skaria
> >
> >
> > "Merlynsdad" wrote:
> >
> > > 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!

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

Merlynsdad wrote:
>
> 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" wrote:
>
> > 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
> > ---------------
> > Jacob Skaria
> >
> >
> > "Merlynsdad" wrote:
> >
> > > 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!

--

Dave Peterson

On Error GoTo 0

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

"Merlynsdad" wrote:

> 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" wrote:
>
> > 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
> > ---------------
> > Jacob Skaria
> >
> >
> > "Merlynsdad" wrote:
> >
> > > 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!