Vlookup property missing

[Jennifer]

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.

 

[Stefi]

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:

 

[OssieMac]

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

 

[Chip Pearson]

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