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

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

--
HTH,
Barb Reinhardt



"aligatrjoe" wrote:

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

One way:

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

In article ,
aligatrjoe wrote:

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.

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


"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt



"aligatrjoe" wrote:

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

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


"JE McGimpsey" wrote:

One way:

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

In article ,
aligatrjoe wrote:

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.