(VBA) Vlookup on multiple files & sheets

[andrea_antorini]

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

 

[offthelip]

try this:

myfile = "Pianificazione 2021 dettagliata originale.xlsx"
mysheet = "Hausammann"
Range("C47:c383").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'[" & myfile & "]" & mysheet & "'!R22C2:R70C5,2,FALSE),0)"