Using VLOOKUP in VBA

[wrgallo]

I want to use a function like VLOOKUP in VBA code. So I want to be abl
to say:
Sheets("Sheet3").cells(2,2) = Vlookup(Sheets("Sheet1").Cells(1,1),
Sheets("Sheet2").Range("D1:E60"), 2)

I want to look up the value from sheet1 cell A1, in Sheet2 D1 to D60.
If found, return the row from column E on sheet 2 and place result i
Cell B2 in sheet 3.

However, I don't know how to use the Vlookup function from within VBA.
Perhaps one cannot do so.

Thanks,

 

[gitcypher]

Not sure if you can. I've always created a dummy (hidden) worksheet t
do all of my lookups etc., then referenced specific cells from tha
worksheet in my code.

-gitcyphe

 

[Charles Williams]

Hi G

You can either enter a formula from VBA, something like
dim strFormulas as string

strformula="=VLookup(Sheet1!a1,Sheet2!D1:E60,2)"
Sheets("Sheet3").cells(2,2).formula=strformula

or you use the VLOOKUP function to return a value to VBA something like

dim vAnswer as variant
vanswer=application.Vlookup(Sheets("Sheet1").Cells(1,1),
Sheets("Sheet2").Range("D1:E60"), 2)
if not iserror(vanswer) then
Sheets("Sheet3").cells(2,2)=vanswer
endif


--
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Frank Kabel]

Hi
you may use
application.worksheetfunction.vlookup(....)
for this

 

[gitcypher]

I learn something new every day. Thanks from me as well.

-gitcyphe

 

[wrgallo]

Thanks everyone who replied. It helped me get a working version.
I used the solution which had
Dim vAnswer As Variant
in it. Though I am interested in trying out all possibilities later.

Cheers,