Insert range reference (date) into formula-CodePudding

Im just going to include the code I already have. I would like to be able to take the date I have in cell A1 and reference it in the vlookup formula I have. To put the formula with the referenced date in Range(“H3”). If some one could help out I would really appreciate it.

=IFERROR(VLOOKUP($I5,’J:\Optimization\Cut RiteV11\EXPORT\[Dovetail Drw Btms For (Reference to A1).xlsx] Part summary’!$A$6:$G$600,7,0),0)

CodePudding user response：

Use Format$ to format the date, and & to concatenate it into the formula.

Also, use straight single and double quotes: ' and ", not ’ and “”.

Dim dt As String
dt = Format$(Range("A1").Value, "mm-dd-yyyy") ' change as necessary

Range("H3").Formula = "=IFERROR(VLOOKUP($I5,'J:\Optimization\Cut RiteV11\EXPORT\[Dovetail Drw Btms For " & dt & ".xlsx] Part summary'!$A$6:$G$600,7,0),0)"