VLOOKUP Expeected: end of statement Error

[Jett Midknight]

Hello all. So I am trying to run a vlookup from yesterdays file, however as I want the file name to be easily changed, I have assigned it to a variable, rather than having to change 10 lines of code every time you want to change the name. The problem I am running into is getting an excepted end of statement error. I'm sure it is simple as a misplaced comma or quotation, but I can't seem to find it. Any help would be appreciated.

Dim FN As String
FN = "Test Schedule "

Range("C2").FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[FN & Format(Application.WorkDay(Date, -1), "m-dd-yy") & ".xlsx]Schedule'!R1C1:R101C4,4,FALSE)"

I have also tried it in this form, but it then won't recognize that yesterdays file is open.

Dim FN As String
FN = "Test Schedule "

Range("C2").FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[FN" & Format(Application.WorkDay(Date, -1), "m-dd-yy") & ".xlsx]Schedule'!R1C1:R101C4,4,FALSE)"

 

[Jett Midknight]

I managed to fix it. Apparently the proper format it

[COLOR=#333333]Range("C2").FormulaR1C1 = _
[/COLOR][COLOR=#333333]        "=VLOOKUP(RC[-2],'[" & FN & Format(Application.WorkDay(Date, -1), "m-dd-yy") & ".xlsx]Schedule'!R1C1:R101C4,4,FALSE)"[/COLOR]