Need macro to add 100 years to a cell's date

[Guest]

I am using Office 2003. I know how to write a formula in a new cell and set
it equal to a different cell's date plus 100 years. I can then copy new
formula and paste special to original cell. But I sure can't figure out how
to do this with a macro. I am thinking I declare a variable as myDate, set
myDate = Cell(A1), then add 100 years to myDate, then pastespecial to
cell(a1). But I can't get this to work. Any help would be greatly
appreciated.

 

[Guest]

Try this

Sub Add100()
Dim myDate As Date
myDate = Range("A1").Value
myDate = DateSerial(Year(myDate) + 100, Month(myDate), Day(myDate))
Range("A1").Value = myDate

End Sub

 

[JE McGimpsey]

One way:

With Range("A1")
.Value = DateAdd("yyyy", 100, .Value)
End With

 

[Guest]

Thank you Barb Reinhardt. Just what I was looking for. I could never find
the function DateSerial.

 

[Guest]

Thanks JE McGimpsey. This gives me even more food for thought. I have
received two answers within 3 hours. I am very impressed with the help that
is available out there. Thanks again.