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)