Using Vlookup in VBA

[prahz]

In excel I have created a user form, in which there is a label
("nonfincalcLabel"). I am trying to change the Caption of the label
based on a calculation using Vlookup when a change is made to one of
the comboboxes. The lookup table range is defined as "nonfinRange" in
the worksheet "nonfinTable".

When i try to run the code it says, Run-time error '1004': Unable to
get the Vlookup property of the WorksheetFunction class. Below is my
code, where am i going wrong?

Private Sub defaulthistoryCombo_Change()
Dim searchRange As Range

Set searchRange = Worksheets("nonfinTable").Range("nonfinRange")

nonfincalcLabel.Caption =
Application.WorksheetFunction.VLookup(xCombo.Value, searchRange, 3,
False) + Application.WorksheetFunction.VLookup(yCombo.Value,
searchRange, 5, False)

End Sub

 

[Dave Peterson]

Dim res1 as variant 'may return an error (like #n/a in the worksheet
dim res2 as variant
dim myStr as string

res1 = application.vlookup(xcombo.value, searchrange, 3, false)
res2 = application.vlookup(ycombo.value, searchrange, 5, false)

if iserror(res1) _
or iserror(res2) then
mystr = "Some kind of error with vlookup"
elseif isnumeric(res1) _
and isnumeric(res2) then
mystr = format(res1 + res2, "#,###.00") 'or no format???
else
mystr = "at least one non-numeric found"
end if

nonfincalcLabel.Caption = mystr

====
Untested, uncompiled. Watch for typos.

Application.vlookup() returns an error that you can test with iserror().

application.worksheetfunction.vlookup() causes a runtime error that you have to
catch.

dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(....)
if err.number <> 0 then
'an error was found
err.clear
else
'no error!
end if
on error goto 0

application.vlookup() looks/works much easier (well, to me).