Adding exactly 1 year to a date in VBA

[Iceberg]

Hello,

I have a spreadsheet with a column of dates and a command button. When I press the command button, I would like the macro to look at the dates and add exactly 1 year to each of them. How would I go about doing this?

Thanks!

 

[Scott Huish]

Perhaps:

Private Sub CommandButton1_Click()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If IsDate(c) Then c = DateAdd("yyyy", 1, c)
Next
End Sub

 

[Jonmo1]

This assumes:
Your dates are in column A
You have a header in row 1, If Not, change
for i = 2 to LR
to
for i = 1 to LR

Private Sub CommandButton1_Click()
Dim LR As Long
Dim x As Date
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LR
        x = Cells(i, "A").Value
        Cells(i, "A").Value = DateSerial(Year(x) + 1, Month(x), Day(x))
Next i
End Sub

Hope this helps...

 

[Iceberg]

It's working now! Thanks a bunch!

 

[Iceberg]

Follow-up question...I would like the macro to look at a date and add increments of 5 years until the date is past today's date.

Here's the general idea:

X = ActiveCell.Value
Do Until ActiveCell.Value > (TodaysDate)
  ActiveCell.Value = DateSerial(Year(x) + 5, Month(x), Day(x))
Loop

Any suggestions?

 

[Scott Huish]

Sub test()
Do Until ActiveCell > Date
    ActiveCell = DateAdd("yyyy", 5, ActiveCell)
Loop
End Sub

 

[draggar]

I registered to ask the same question. Interesting that Google didn't pick this one up but the code that HotPepper supplied (with some modification) is exactly what I was looking for.

(Thank you!)*10