Long story short, automating VBA code so I can reference previous files without having to edit the code. The file names are based off the current date. All works well when it's a two digit month, but single digits get lost in the translation.
Here is the code: Range(Cells(2, 5), Cells(FinalRow, 5)).FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,<directory (excluded for clarity)> [" & MonthNum & "-" & YearNum & " Final Pricing.xlsb]Final'!C1:C17,13,FALSE),"""")"
I've tried to format MonthNum (e.g. MonthNum = Format(MonthNum, "00"), plus multiple other formatting variants, but this throws an error. I'm assuming it's because MonthNum is seen by VBA as a string after formatting, thus need some combination of ampersands and quotes, but I can't get it to work. If I don't format MonthNum, the VBA code works, but the file isn't found (it's saved as mm-yy with leading zeroes).
Thoughts?
Here is the code: Range(Cells(2, 5), Cells(FinalRow, 5)).FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,<directory (excluded for clarity)> [" & MonthNum & "-" & YearNum & " Final Pricing.xlsb]Final'!C1:C17,13,FALSE),"""")"
I've tried to format MonthNum (e.g. MonthNum = Format(MonthNum, "00"), plus multiple other formatting variants, but this throws an error. I'm assuming it's because MonthNum is seen by VBA as a string after formatting, thus need some combination of ampersands and quotes, but I can't get it to work. If I don't format MonthNum, the VBA code works, but the file isn't found (it's saved as mm-yy with leading zeroes).
Thoughts?