Working function won't insert with VBA

[mharper90]

I'm trying to fill down two columns with a VLOOKUP function. The basic VLOOKUP function works, but when I try to use the TEXT function to ensure the date gets it is "looking up" is returned as m/dd/yyyy instead of a large integer, VBA won't even let me continue writing the code before it tells me that it is incorrect. It highlights the line in red, and then cursor highlights the m in m/dd/yyyy, stating that it's looking for an ending. The VLOOKUP without the TEXT function works with VBA, and the VLOOKUP with the TEXT function works when placed directing into the cell, but I just can't get VBA to put the formula there for me.

Lr = Range("A" & Rows.Count).End(xlUp).Row

With ws2.Range("C3:C" & Lr)
.Cells.Formula = "=&TEXT(VLOOKUP(A3, 'Main Data'!B:N,4,FALSE), "m/dd/yyyy")"
.Cells.FillDown
End With

With ws2.Range("D3:D" & Lr)
.Cells.Formula = "=&TEXT(VLOOKUP(A3, 'Main Data'!B:N,5,FALSE), "m/dd/yyyy")"
.Cells.FillDown
End With

Anyone have a resolution to fix this or somehow accomplish the same intent? Thanks!

 

[Rick Rothstein]

I'm trying to fill down two columns with a VLOOKUP function. The basic VLOOKUP function works, but when I try to use the TEXT function to ensure the date gets it is "looking up" is returned as m/dd/yyyy instead of a large integer, VBA won't even let me continue writing the code before it tells me that it is incorrect. It highlights the line in red, and then cursor highlights the m in m/dd/yyyy, stating that it's looking for an ending. The VLOOKUP without the TEXT function works with VBA, and the VLOOKUP with the TEXT function works when placed directing into the cell, but I just can't get VBA to put the formula there for me.

Lr = Range("A" & Rows.Count).End(xlUp).Row

With ws2.Range("C3:C" & Lr)
.Cells.Formula = "=&TEXT(VLOOKUP(A3, 'Main Data'!B:N,4,FALSE), "m/dd/yyyy")"
.Cells.FillDown
End With

With ws2.Range("D3:D" & Lr)
.Cells.Formula = "=&TEXT(VLOOKUP(A3, 'Main Data'!B:N,5,FALSE), "m/dd/yyyy")"
.Cells.FillDown
End With

Anyone have a resolution to fix this or somehow accomplish the same intent? Thanks!

Quote marks (not apostrophes) must be doubled up when they occur inside quote marks used to define a text constant. So change this...

"m/dd/yyyy"

to this...

""m/dd/yyyy""

inside the formula.