Forum Discussion
Solved
Excel add 1 month to a column of dates
Hi I have a spreadsheet that I use every month and copy over the sheet for the next month but have to go down the date column to manually change the date by one month. Is there a formula that I can u...
You could run a macro:
Sub AddMonth() Dim r As Long Dim m As Long Application.ScreenUpdating = False m = Range("B" & Rows.Count).End(xlUp).Row For r = 2 To m Range("B" & r).Value = DateAdd("m", 1, Range("B" & r).Value) Next r Application.ScreenUpdating = True End Sub
Hans , Peter's first suggestion is to complex for me and because the dates are not consecutive (I have tried) your suggestion but it did not work for me.
P.S. Why would you want to have a date in 3033? <grin>
- Thats why I want to do it automatically ☹️. I am going to try your macro, it will take some time , 20 years since I did any programming so will need to do some research.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy/paste the code into the module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
To run the macro, press Alt+F8, select AddMonth and click Run.
Thanks Hans HansVogelaar that works, now I just to adapt the Macro to my worksheet.
It needs to start at column C row 6 and with the following changes I'm getting errors in row 6 of the Macro, or at least thats were it is highlighting. But the original Macro using column B row 1 works fine.
Sub AddMonth() Dim r As Long Dim m As Long Application.ScreenUpdating = False m = Range("C" & Rows.Count).End(xlUp).Row For r = 6 To m Range("C" & r).Value = DateAdd("m", 1, Range("C" & r).Value) Next r Application.ScreenUpdating = True End Sub