andrea_antorini
New Member
- Joined
- Jan 29, 2022
- Messages
- 1
- Office Version
- 2016
- Platform
- 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
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