VBA/VLOOKUP Error Message

[JimFor]

Hi,

I'm trying to us the VLOOKUP function in a VBA program. Decided to try a
simple code so see how things work and I can't get it to work. I am working in
Sheet 1 of a workbook. Typed the following VBA code:

"Sub ZOO()
Dim INVEN As Double
Cells(1, 3) = VLookup (A1, INVEN, 2)
End Sub"

When I run it I get a compile error telling me that the Sub or Function is not
defined. I thought the VLOOKUP function would automatically be recogized by
the program. I admit I am a novice at this but I am slowly learning. Can
anyone tell me what I am doing wrong? BTW, I type VLOOKUP in all caps and the
program changes some letters to small.

Thanks

 

[Norman Jones]

Hi JimFor,

Try:

Application.WorksheetFunction.Vlookup

 

[JimFor]

Thanks. Where exactly do I place that in the formula? Before the Dim
statement? Before the Cells statement? Add it to a particular line?

 

[George Nicholson]

Cells(1, 3) = Application.WorksheetFunction.Vlookup (A1, INVEN, 2)

and you'll probably need to be more specific (VBA usually won't intrepret A1
or INVEN as easily as Excel does when used in a cell formula):

Cells(1, 3) = Application.WorksheetFunction.Vlookup (Range("A1").Value,
Range("INVEN"), 2)

or even even more specific....:

Cells(1, 3) = Application.WorksheetFunction.Vlookup
(Worksheets("Sheet1").Range("A1").Value,
Worksheets("Sheet2").Range("INVEN"), 2)

Hope this doesn't scare you off,

 

[JimFor]

Thanks much. Nope. I'm not scared. The more I do the more I understand and
the more II do the more I don't understand. But slowly the "understand": part
is growing larger.