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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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,344
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,054
Messages
5,835,153
Members
430,342
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top