Vlookup variables

[Smeggy]

Hi All,
Hopefully this should be quick and simple and ive probaby asked about it before but; this is the original vlookup:


Range("D" & a).Formula = "=VLOOKUP(" & Range("A" & a) & ",'[17-09-2014_Name.xls]9. MEAS Site Detail'!$B:$X,23,FALSE)"


What i need to do is add a variable date i.e. Now -1 (yesterdays date)
This is what ive got but desn't work


Range("D" & a).Formula = "=VLOOKUP(" & Range("A" & a) & ",'(Format(now - 1, "dd-mm-yyyy")) & ["_Name.xls"]9. MEAS Site Detail'!$B:$C,2,FALSE)"

Any help would be appreciated.

 

[Comfy]

Give this a try:

Range("D" & a).Formula = "=VLOOKUP(" & Range("A" & a) & ",'[" & Format(now() - 1, "dd-mm-yyyy") & "_Name.xls]9. MEAS Site Detail'!$B:$C,2,FALSE)"