Add current year in VBA concatenate formula

[Serafin54]

Hello,

I have this code and want to make the 2023 dynamic so it's not having to be remembered to update as it were in this case 2 months into the new year. I have tried different combinations of Dim As and =Year(Now) but i'm hitting a roadblock. it is also possible i am doing this the most painful and primitive way possible so i am sorry if my code is butt ugly. It's makeshift and unrefined i'm sure.

 Range("A2").Resize(Cells(Rows.Count, "G").End(xlUp).Row - 1).Formula = "=CONCATENATE(G2,""-"",H2,""_"",J2,""_2023"",""_WDI"")"

Thanks

 

[DanteAmor]

Try this:

Range("A2").Resize(Cells(Rows.Count, "G").End(xlUp).Row - 1).Formula = "=CONCATENATE(G2,""-"",H2,""_"",J2,""_" & Year(Date) & """,""_WDI"")"

Or this:

Sub test3()
 Range("A2").Resize(Cells(Rows.Count, "G").End(xlUp).Row - 1).Formula = "=CONCATENATE(G2,""-"",H2,""_"",J2,""_"" & Year(TODAY()) & ""_WDI"")"
End Sub

Or this:

Sub test4()
 Range("A2").Resize(Cells(Rows.Count, "G").End(xlUp).Row - 1).Formula = "=G2&""-""&H2&""_""&J2&""_""&YEAR(TODAY())&""_WDI"""
End Sub

 

[Serafin54]

Try this:

Range("A2").Resize(Cells(Rows.Count, "G").End(xlUp).Row - 1).Formula = "=CONCATENATE(G2,""-"",H2,""_"",J2,""_" & Year(Date) & """,""_WDI"")"

Or this:

Sub test3()
 Range("A2").Resize(Cells(Rows.Count, "G").End(xlUp).Row - 1).Formula = "=CONCATENATE(G2,""-"",H2,""_"",J2,""_"" & Year(TODAY()) & ""_WDI"")"
End Sub

Or this:

Sub test4()
 Range("A2").Resize(Cells(Rows.Count, "G").End(xlUp).Row - 1).Formula = "=G2&""-""&H2&""_""&J2&""_""&YEAR(TODAY())&""_WDI"""
End Sub

Thank you very much!