VBA Code for Multiple VLOOKUPS

[jumpingbean]

hi...hi...hi...

How can I programatically write to VLOOKUP in multiple workbooks?
I have 50 workbooks to use in for my VLOOKUPS.

It second FOR statement runs, but skips the first FOR statement.
So it successfully VLOOKUPS:
"=VLOOKUP(RC[-2],[state2.xls]Sheet1!R1C1:R7C3,3,FALSE)"

It fails to run:
"=VLOOKUP(RC[-2],[state1.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!!!

 

[Norie]

Sorry but what's the question/problem?

What are you actually trying to do?

What formulas are you trying to put in the cells?

 

[jumpingbean]

hi...

I have a master Excel workbook (one sheet) that I need to VLOOKUP against 50 individual workbooks in order to return a value.
The VLOOKUP formula calculates successfully.

Goal: Run multiple VLOOKUPS formulas in the VBA code in the master workbook.

thanks!!

 

[Norie]

I'm sorry but it still not clear what you want.

I can tell you one thing though the 2nd loop is overwriting the VLOOKUP's created by the 1st one.

 

[jumpingbean]

Yes, it appears the 2nd vlookup is overwriting the 1st.
That is my problem.
How can I stop it from overwriting the 1st vlookup?
Can I excute multiple vlookups simultaneously?

THIS IS THE SCENARIO: (pardon for misclarification)

MASTER WORKBOOK:
Column A Column B
1 value = [vlookup WORKBOOK 1]
2 value = [vlookup WORKBOOK 2]
3 value = [vlookup WORKBOOK 3]

***The code will need to excute the VLOOKUPS to return value in Column B of MASTER WORKBOOK***


WORKBOOK 1
Column A Column B
1 new york

WORKBOOK 2
Column A Column B
2 miami

WORKBOOK 3
Column A Column B
3 new jersey


thx...