VBA Code for Multiple VLOOKUPS

jumpingbean

New Member
Joined
Dec 12, 2005
Messages
8
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!!!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,052
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 12, 2005
Messages
8
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,052
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 12, 2005
Messages
8
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,287
Messages
5,571,318
Members
412,382
Latest member
Langtn02
Top