Insert a formula with string variables in a cell using vba

I've set a Date variable to workday -2

Im using a String to format that Date variable using format(date, "mm/yyyy")

Im trying to add this String variable into a formula im pasting into an excel cell using VBA but it adds quotations around my String variable so the formula doesnt work.

e.g Range("Z2:Z" & Lastrow).formula = "=vlookup(y2, filename_""" & Date Name & """.xlsx"

I'm currently double quot开发者_开发技巧ing my string variable within the formula as a single quote leads to an error.

Im expecting the date to show up in the formula without being surrounded by quotation marks

The triple double quotes are concatenating a double quote to your string.

Change

 Range("Z2:Z" & Lastrow).formula = "=vlookup(y2, filename_""" & Date Name & """.xlsx"

to

 Range("Z2:Z" & Lastrow).formula = "=vlookup(y2, filename_" & Date Name & ".xlsx"