Forum Discussion

Copper Contributor

Mar 18, 2022

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...

excel

HansVogelaar

Mar 19, 2022

kelvinmc

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

kelvinmc

Copper Contributor

Mar 19, 2022

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

HansVogelaar

MVP

Mar 19, 2022

kelvinmc

You ran two lines together.

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

kelvinmc
Copper Contributor
Mar 20, 2022
Thank you HansVogelaar . In the end the problem with the sub was down to my data. ie date not in right format. Just need to add some error checking