Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup property missing
I am using this code to try and return a value in column d based from text in
the cboRation combobox. i am getting error Unable to get Vlookup property of the WorksheetFunction class Here is the code i am try to use Private Sub GetSum() Dim Ration As Variant Dim Percent As String Ration = cboRation.Value Sheets("Rations").Activate Percent = WorksheetFunction. _ VLookup(Ration, Range("PercentList"), 3, False) me.lblPercent.valueu= Percent End Sub Thank you so much. -- Thank you, Jennifer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup property missing
I cannot be sure, but VBA sends such an error message if Vlookup doesn't find
the value searched for. Check existence of "Ration" or use On Error! Regards, Stefi €˛Jennifer€¯ ezt Ć*rta: I am using this code to try and return a value in column d based from text in the cboRation combobox. i am getting error Unable to get Vlookup property of the WorksheetFunction class Here is the code i am try to use Private Sub GetSum() Dim Ration As Variant Dim Percent As String Ration = cboRation.Value Sheets("Rations").Activate Percent = WorksheetFunction. _ VLookup(Ration, Range("PercentList"), 3, False) me.lblPercent.valueu= Percent End Sub Thank you so much. -- Thank you, Jennifer |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup property missing
Hi Jennifer,
You also get that message if Ration is a string and you are looking for a numeric. Dim Ration As Variant: will cause a string to be returned from the Combo box even though it has numerics. If it is numerics in the combo then: Dim Ration As Long will fix the problem -- Regards, OssieMac "Stefi" wrote: I cannot be sure, but VBA sends such an error message if Vlookup doesn't find the value searched for. Check existence of "Ration" or use On Error! Regards, Stefi €˛Jennifer€¯ ezt Ć*rta: I am using this code to try and return a value in column d based from text in the cboRation combobox. i am getting error Unable to get Vlookup property of the WorksheetFunction class Here is the code i am try to use Private Sub GetSum() Dim Ration As Variant Dim Percent As String Ration = cboRation.Value Sheets("Rations").Activate Percent = WorksheetFunction. _ VLookup(Ration, Range("PercentList"), 3, False) me.lblPercent.valueu= Percent End Sub Thank you so much. -- Thank you, Jennifer |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup property missing
Jennifer,
If you use Application.WorksheetFunction.VLookup, you will get a run time error if the lookup value is not found within the lookup range. Thus, if the value Ration is not found in the range PercentList, you'll get an error. Thus you should trap the error with code like Dim ErrNum As Long On Error Resume Next Err.Clear Percent = Application.WorksheetFunction.VLookup(Ration, Range("PercentList"), 3, False) ErrNum = Err.Number On Error GoTo 0 If ErrNum < 0 Then Debug.Print "Not Found" '''''''''''''''''''''''''''''''' ' code for not found condition '''''''''''''''''''''''''''''''' Exit Sub Else '''''''''''''''''''''''''''''''' ' code for found '''''''''''''''''''''''''''''''' Debug.Print "Found: " & Percent End If You can omit the need for run-time error trapping with On Error by omitting the "WorksheetFunction" from the code and declaring Percent as a Variant data type. Use code like Dim Percent As Variant '<<< must be Variant type Percent = Application.VLookup(Ration, Range("PercentList"), 3, False) If IsError(Percent) = True Then Debug.Print "Not Found" '''''''''''''''''''''''''''''' ' code for not found '''''''''''''''''''''''''''''' Else Debug.Print "Found: " & Percent '''''''''''''''''''''''''''''' ' code for found '''''''''''''''''''''''''''''' End If The difference between these code examples is that the first uses "WorksheetFunction" in the call to VLookup and the second does not. If WorksheetFunction is present and the value is not found, a trappable run-time error is raised and must be handled with an On Error statement. If WorksheetFunction is not present, and the value is not found, no run time error is raised (so On Error is irrelevant) and the return value is an Error-type Variant (you must declare the result variable As Variant). You can test whether a Variant contains an error type value with IsError(). See http://www.cpearson.com/excel/Callin...ionsInVba.aspx for more details, especially the section on error handling. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Jennifer" wrote in message ... I am using this code to try and return a value in column d based from text in the cboRation combobox. i am getting error Unable to get Vlookup property of the WorksheetFunction class Here is the code i am try to use Private Sub GetSum() Dim Ration As Variant Dim Percent As String Ration = cboRation.Value Sheets("Rations").Activate Percent = WorksheetFunction. _ VLookup(Ration, Range("PercentList"), 3, False) me.lblPercent.valueu= Percent End Sub Thank you so much. -- Thank you, Jennifer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to get the Vlookup property of the WorksheetFunction class | Excel Programming | |||
ActiveX property missing | Excel Programming | |||
Unable to get the Vlookup Property of the WorkSheetFunction Class | Excel Programming | |||
Width Property missing from Listview (mscomctl.dll) in VB6 | Excel Programming | |||
Unable to get VLOOKUP property | Excel Programming |