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.

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.

I think Bob copied your formula and the column to be returned was omitted:

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

(still untested)

Bob Phillips wrote:

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.





--

Dave Peterson

JimFor wrote:
Hi,
. . .
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.

If you insert "Stop" (without the quotes) after the first line of your
code, the code will stop and you will have an opportunity to step
through it with the DeBug Window visible. Look up the On-line VBA help
for Debug Toolbar.

Alan Beban

Thanks for the help. From what I have programmed, the solution seems to work.
I will also try inserting "Stop" in programs and see if I can learn more about
what has gone wrong when some code does not produce results.