VBA Code for Multiple VLOOKUPS

[Myrna Rodriguez]

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!!!

 

[Guest]

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