VLOOKUP in VBA

[Stuart Grant]

On the worksheet I can insert in a cell
=VLOOKUP(C5, Hobokee.xls!AcsLow, 2)
and it works perfectly, looks up the value in column 2 of the range named
AcsLow in the same workbook.
But elswhere I want in a macro to lookup the same table and assign the
result to a variable BalAmt.
BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work.
It gives a function not defined error on Hobokee. If instead of Hobokee I
put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP
which it changes to VLookup.
I have been overVLOOKUP in the Help file and see nothing wrong.
What is wrong ?
Stuart

 

[Barry-Jon]

VLookup is a worksheet function so you need to specify it as such;

WorksheetFunction.VLookup(etc...)

 

[Dave Peterson]

Dim res as variant
dim lookupRng as range
dim lookupVal as range

set lookuprng = workbooks("hobokee.xls").range("acsLow")

set lookupval = workbooks("otherbook.xls").worksheets("sheet99").range("c5")

res = application.vlookup(lookupval,lookuprng,2)

if iserror(res) then
'same as #n/a
msgbox "not found"
else
msgbox res
end if

 

[Stuart Grant]

Barry-Jon

Thanks for your prompt help. Pity that when you look up VLookup in VBA
help, there is no mention of WorkshopFunction.
I had a little trouble with the range too but have got this sorted out.

Stuart

 

[Dave Peterson]

Look for worksheetfunction in VBA's help.

But for any function, you'll find the Excel's help (not VBA's) is where you'll
want to check.