Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code for Multiple VLOOKUPS
hi...hi...hi... How can I programatically write to VLOOKUP in multiple workbooks? I have 50 workbooks to use in for my VLOOKUPS. It fails to VLOOKUPS 1st FOR statement: "=VLOOKUP(RC[-2],[state1.xls]Sheet1!R1C1:R7C3,3,FALSE)" It successfully VLOOKUPS 2nd FOR statement: "=VLOOKUP(RC[-2],[state2.xls]Sheet1!R1C1:R7C3,3,FALSE)" This is my code: Sub Look() For x = 1 To 100 Cells(x, 3).Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-2],[state1.xls]Sheet1!R1C1:R7C3,3,FALSE)" Next x For x = 2 To 11 Cells(x, 3).Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-2],[state2.xls]Sheet1!R1C1:R7C3,3,FALSE)" Next x End Sub Thanks a bunch for your help and continue to enjoy life!!! *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code for Multiple VLOOKUPS
I ran the code and it worked ok for me.
Have you worksheets in the current folder called state1.xls and state2.xls? Also it is nicer to write the code as follows: Sub Look() For x = 1 To 100 Cells(x, 3).FormulaR1C1 = "=VLOOKUP(RC[-2],[state1.xls]Sheet1!R1C1:R7C3,3,FALSE)" Next x For x = 2 To 11 Cells(x, 4).FormulaR1C1 = "=VLOOKUP(RC[-3],[state1.xls]Sheet1!R1C1:R7C3,3,FALSE)" Next x End Sub "Myrna Rodriguez" wrote: hi...hi...hi... How can I programatically write to VLOOKUP in multiple workbooks? I have 50 workbooks to use in for my VLOOKUPS. It fails to VLOOKUPS 1st FOR statement: "=VLOOKUP(RC[-2],[state1.xls]Sheet1!R1C1:R7C3,3,FALSE)" It successfully VLOOKUPS 2nd FOR statement: "=VLOOKUP(RC[-2],[state2.xls]Sheet1!R1C1:R7C3,3,FALSE)" This is my code: Sub Look() For x = 1 To 100 Cells(x, 3).Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-2],[state1.xls]Sheet1!R1C1:R7C3,3,FALSE)" Next x For x = 2 To 11 Cells(x, 3).Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-2],[state2.xls]Sheet1!R1C1:R7C3,3,FALSE)" Next x End Sub Thanks a bunch for your help and continue to enjoy life!!! *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple VLOOKUPS | Excel Discussion (Misc queries) | |||
Multiple VLookups - Can anyone help me please? | Excel Discussion (Misc queries) | |||
Sum a row of multiple vlookups | Excel Worksheet Functions | |||
Multiple Vlookups | Excel Worksheet Functions | |||
multiple vlookups | Excel Worksheet Functions |