(VBA) Vlookup on multiple files & sheets

andrea_antorini

New Member
Joined
Jan 29, 2022
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
H there,
I am struggling with some piece of VBA code in Excel.

This is the formula in my Excel Sheet, this is the sheet who receive the information:
=IF.ERROR(VLOOKUP(B48;'[Pianificazione 2021 dettagliata originale.xlsx]Hausammann'!$B$22:$E$70;2;FALSO);0)

Which has been translated by the (Macro recorder) in the following:
Range("C47").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'[Pianificazione 2021 dettagliata originale.xlsx]Hausammann'!R22C2:R70C5,2,FALSE),0)"
Along from C47 to C383
Range("C383").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'[Pianificazione 2021 dettagliata originale.xlsx]Hausammann'!R22C2:R70C5,2,FALSE),0)"
And all works fine!

Now, here comes the troubles.

I have two variables the name of the workbook which changes over the years in red in the above code, and the sheet name which refers to the name of people in green in code above.

In my code I have some declared variables which are myfile and mysheet

Could somebody help me and explain how could I insert these variables in the above code?
myfile = Pianificazione 2021 dettagliata originale.xlsx
mysheet = Hausammann

Thank you for any help
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
try this:
VBA Code:
myfile = "Pianificazione 2021 dettagliata originale.xlsx"
mysheet = "Hausammann"
Range("C47:c383").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'[" & myfile & "]" & mysheet & "'!R22C2:R70C5,2,FALSE),0)"
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top