For a report I want to find values in a different Workbook by using vlookup in a macro. This part works for me but the problem is that the workbook changes in name every week according to the weeknumber. I can't get this to work in a vlookup formula.
With this formula it works:
Range("D36").FormulaR1C1 = _
"=IFERROR(VLOOKUP(""Monday"",'[orders 201739.xlsx]Blad1'!R2C1:R8C2,2,FALSE),0)"
But my filename changes per week, so the for the next week the formula should need to be:
Range("D36").FormulaR1C1 = _
"=IFERROR(VLOOKUP(""Monday"",'[orders 2017340.xlsx]Blad1'!R2C1:R8C2,2,FALSE),0)"
Is there a way to change this week number in the vlookup using vba?
Both year (cell R2) and week number (cell E7) are stated in the report workbook.
Thanks in advance!
Bookmarks