Jim,

Not tested, but try this

Cells(5, 8) = WorksheetFunction.VLookup(Activesheet.Range("A3"),
Worksheets("Sheet1").Range("A2:C2"), 0)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JimFor" wrote in message
...
Hi,

I'm trying to use the VLOOKUP function in VBA. I am learning but still
have
some problems. I tried various codes with limited success. Then I just
decided to type the forumla I used "manually" into the VBA program and see
what
happened . .
This is the formula. It works when I use it manually.

= VLOOKUP(A3,Sheet1! $A$2:$C$3,2,0)

I added the words "Worksheet.Function'" and tried it in the VBA program.
The
formula was to find a value and put it in Cell (5,8) of Sheet 2 which was
the
active sheet. The formula did not work. The program gave me an error
message which stated it did not recognize the $ characters. If I took
them
out I got a syntax error. I then decided to change the forumla and put
Range
data.

Sub ZOO()
Dim INVEN As Double
Cells(5, 8) = WorksheetFunction.VLookup(Range("A3"),
Sheet1!Range("A2:C2"), 0)
End Sub
(I have tried adding the code ".Value" after both range indicators)


This time I got the Macro run box. Ran the program and I got the
following
error message:

"Object does not suppor this property or method."

Can anyone tell me how to get the VLOOKUP function to work in my VBA
program?
Why does a formula work manually and not when I enter it into VBA coding?

Also, I have learned that it is a good idea to use the Macro recorder to
see
what code might work for something you are trying to do in VBA. How can I
do
that for something like this? I don't know how to see what the program
does
when I do it manually. I just enter code and I get the number.

Thanks for any help.