VBA VLOOKUP Question
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. |
VBA VLOOKUP Question
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. |
VBA VLOOKUP Question
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 |
VBA VLOOKUP Question
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 |
VBA VLOOKUP Question
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. |
All times are GMT +1. The time now is 03:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com