date today formula 1004 error in vba

[jaik22]

  Columns("U:U").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("U1").Value = "Data"
        Range("U2").Select
            ActiveCell.FormulaR1C1 = "=DATE(Month(TODAY())&Date(TODAY()-20)& &LOW&L2&_INV&S2"
                Selection.AutoFill Destination:=Range("U2:U" & lastrow)
                    Range("U2:U" & lastrow).Select
                    Selection.Copy
                        Range("U2:U" & lastrow).Select
                        ActiveCell.PasteSpecial

For now. I have that code for getting data in U column.
I am trying to use date today formula to get date 20 days earlier. Then I am trying to copy whole row and paste in same column in order to remove formulas. However, I am getting run-time error '1004';

Is there any way to solve this problem?

Thank you!

 

[Takae]

You need to put year in Date formula.
Please run this code. Especially last two lines close to what you want?

Sub test()
Range("A1").Value = "=date(month(today()),day(today()-20),2017)"
Range("A2").Value = "=today()-20"
Range("A3:A10").Formula = "=date(month(today()),day(today()-20),2016)"
Range("B3:B10").Value = Range("A3:A10").Value
Range("A11").Value = "=DATE(MONTH(TODAY()),day(TODAY()-20),2017) & ""_LOW"""
Range("A12").Value = "=text(DATE(2017,MONTH(TODAY()),TODAY()-20),""mm/dd/yyyy"") & ""_LOW"""
End Sub

 

[jaik22]

Thank you for your advise. But code you provided me doesn't work if I add column values like L2, and B2 like this
Range("A12").Value = "=text(DATE(2017,MONTH(TODAY()),TODAY()-20),""mm/dd/yyyy"") & ""_LOW"" &L2&"_"&B2
I get the error message in the cell. Is there way to put column values in the formula?

Thank you for your support!

 

[Takae]

Not single, make it double ""_""
And it was my mistake. It needs Day formula Day(Today()-20).

 

[jaik22]

Thank you so much! It worked way I wanted