I would like to add one month to a date in a cell overwriting it wit
the new date. For example Cell A1 has the date 9/1/2004 and when m
macro is run A1 becomes 10/1/2004.

I can easily do this with the DATE(Year(a1),Month(a1)+1,day(a1) in
cell but I need to run it from the macro and overwrite the existin
cell.

Any help is appreciated

--
bugg
-----------------------------------------------------------------------
buggy's Profile: http://www.excelforum.com/member.php...fo&userid=1666
View this thread: http://www.excelforum.com/showthread.php?threadid=31882

buggy,
Try this

Sub ChangeDate()
Dim Newdate
Newdate = DateSerial(Year(Range("a1")), Month(Range("a1")) + 1,
Day(Range("A1")))
ActiveSheet.Range("a1").Value = Newdate
End Sub

Neil

"buggy" wrote in message
...

I would like to add one month to a date in a cell overwriting it with
the new date. For example Cell A1 has the date 9/1/2004 and when my
macro is run A1 becomes 10/1/2004.

I can easily do this with the DATE(Year(a1),Month(a1)+1,day(a1) in a
cell but I need to run it from the macro and overwrite the existing
cell.

Any help is appreciated.


--
buggy
------------------------------------------------------------------------
buggy's Profile:
http://www.excelforum.com/member.php...o&userid=16666
View this thread: http://www.excelforum.com/showthread...hreadid=318823

Watch the line wrap for newdate
Neil

"Neil" wrote in message
...
buggy,
Try this

Sub ChangeDate()
Dim Newdate
Newdate = DateSerial(Year(Range("a1")), Month(Range("a1")) + 1,
Day(Range("A1")))
ActiveSheet.Range("a1").Value = Newdate
End Sub

Neil

"buggy" wrote in message
...

I would like to add one month to a date in a cell overwriting it with
the new date. For example Cell A1 has the date 9/1/2004 and when my
macro is run A1 becomes 10/1/2004.

I can easily do this with the DATE(Year(a1),Month(a1)+1,day(a1) in a
cell but I need to run it from the macro and overwrite the existing
cell.

Any help is appreciated.


--
buggy
------------------------------------------------------------------------
buggy's Profile:
http://www.excelforum.com/member.php...o&userid=16666
View this thread:
http://www.excelforum.com/showthread...hreadid=318823

Hi
in VBA
with activesheet.range("A1")
..value=dateserial(year(.value),month(.value)+1,da y(.value))
end with


Note: this may give you wrong result for dates such as 31-Jan-2004


--
Regards
Frank Kabel
Frankfurt, Germany

"buggy" schrieb im Newsbeitrag
...

I would like to add one month to a date in a cell overwriting it with
the new date. For example Cell A1 has the date 9/1/2004 and when my
macro is run A1 becomes 10/1/2004.

I can easily do this with the DATE(Year(a1),Month(a1)+1,day(a1) in a
cell but I need to run it from the macro and overwrite the existing
cell.

Any help is appreciated.


--
buggy
---------------------------------------------------------------------
---
buggy's Profile:
http://www.excelforum.com/member.php...o&userid=16666
View this thread:
http://www.excelforum.com/showthread...hreadid=318823