| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cant find VLookup property
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cant find VLookup property
The lookup value is missing...Modify your code to handle that
Dim strSeid As Variant strSeid = Application.VLookup(Worksheets("Temp").Range("$B$3 1"), _ 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cant find VLookup property
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$3 1"), _ 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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cant find VLookup property
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$3 1"), _ 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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cant find VLookup property
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$3 1"), _ 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cant find VLookup property
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$3 1"), _ 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unable to get the VLookup property of the WorksheetFunction class | Excel Programming | |||
| Vlookup problem - unable to get the vlookup property | Excel Programming | |||
| Vlookup property missing | Excel Programming | |||
| Unable to get VLOOKUP property | Excel Programming | |||
| Find OnAction property | Excel Programming | |||
Linear Mode
