Filepath to include today's date within vlookup - excel vba

[AxMan]

Hi All,

I'm basically trying to vlookup values in a spreadsheet, where the spreadsheet has a static title and today's date is included on the end of the static title like so - "Title DD/MM/YYYY".

I am however getting an error on the line highlighted in red (Compile error: syntax error), any help is much appreciated

Sub VlookupAvailability()


'Find last row
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count


Workbooks("Stock Availability for Company.xlsm").Activate
Sheets("A").Select


'Vlookup available stock
Range("K2").Formula = "=IFERROR(VLOOKUP($B2,'C:xxxx\["Title " & Format(Date, "DDMMYYYY") & ".xlsx"]Sheet1'!$A:$L,12,0),0)"
Range(Cells(2, 11), Cells(LastRow, 11)).Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial xlPasteValues


End Sub

 

[KyleUK]

[COLOR=#FF0000]Range("K2").Formula = "=IFERROR(VLOOKUP($B2,'C:xxxx\[[U][B]"[/B][/U]Title " & Format(Date, "DDMMYYYY") & ".xlsx[U][B]"[/B][/U]]Sheet1'!$A:$L,12,0),0)"[/COLOR]

Has unnecessary quote marks - try:

[COLOR=#FF0000]Range("K2").Formula = "=IFERROR(VLOOKUP($B2,'C:xxxx\[Title " & Format(Date, "DDMMYYYY") & ".xlsx]Sheet1'!$A:$L,12,0),0)"[/COLOR]

 

[AxMan]

Thanks Kyle - Worked a treat!