VBA to increase the date based on other cells

[DanielJeff]

I'm after a bit of help with writing a VBA.
I'm putting together a sheet to help finance when to chase based on our 28day invoices.
I have all the invoice dates in column B and am looking to write a VBA to create a new date in column E. This new date will be the invoice date + 28 days.
Is there any way to do this.

F00248	08/02/2023	R28444	41.44
F00315	20/03/2023	R28668	577.26
F00554	16/02/2023	R28520	4035.05
F00614	04/04/2023	R28698	2129.45
F00647	16/01/2023	R27915	1297.30
F01060	15/02/2023	R28517	626.14
F01123	05/01/2023	R27654	4540.09
F01157	18/01/2023	R28043	76.97
F01287	20/02/2023	R28550	534.67
F01700	10/02/2023	R28467	448.98
F01809	01/02/2023	R28371	477.19
F02115	15/03/2023	R28654	549.04
F02205	22/03/2023	R28674	566.10
F02676	14/02/2023	R28494	3989.50
F03012	01/02/2023	R28368	279.36
F03363	09/02/2023	R28458	4181.93
F03553	20/01/2023	R28137	555.46
F03703	04/10/2022	R25426	478.80
F03967	11/04/2023	R28700	496.32

Thanks
Daniel

 

[Joe4]

Welcome to the Board!

Why does it need to be VBA? It can be done with a rather simple formula, i.e. for cell E2 the formula would just be:

=B2+28

If you truly need it in VBA, it is really the same logic, i.e.

Range("E2").Value = Range("B2").Value + 28

 

[Joe4]

If you are looking to update all rows at once, you can use this VBA code:

Sub Insert_Dates()

    Dim lr As Long
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Populate all rows from row 2 to end with formula in column E
    Range("E2:E" & lr).FormulaR1C1 = "=RC[-3]+28"
    
End Sub

 

[DanielJeff]

If you are looking to update all rows at once, you can use this VBA code:

Sub Insert_Dates()

    Dim lr As Long
   
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Populate all rows from row 2 to end with formula in column E
    Range("E2:E" & lr).FormulaR1C1 = "=RC[-3]+28"
   
End Sub

Joe that's a life saver, thanks for that. Works perfectly

Formulas would be a lot simpler but Its all part of a larger VBA i was trying to put together.
The original document needed to have merged cells removed, redundant columns and rows removed and a few cells dropping down. All of that i've been able to do, but was really struggling with how to take the date and add 28 days to it.

 

[Joe4]

Makes sense.
You are welcome.
Glad I was able to help!