I have a macro which clears last years information and makes the worksheet
available for a new year. I want the Macro to increase last year's balance
date by one year. How do you get the Macro to increase the date value of the
active cell by one year?

With Activecell
.Value = Date(Year(.Value)+1,Month(.Value),Day(.Value))
End With

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"pkeegs" wrote in message
...
I have a macro which clears last years information and makes the worksheet
available for a new year. I want the Macro to increase last year's balance
date by one year. How do you get the Macro to increase the date value of
the
active cell by one year?

Hi Bob, thanks for the reply,
I have entered your code into my macro, but when I run the macro I have a
Compile error come up "Expected:)" The helpfile is no help to me!

Regards

"Bob Phillips" wrote:

With Activecell
.Value = Date(Year(.Value)+1,Month(.Value),Day(.Value))
End With

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"pkeegs" wrote in message
...
I have a macro which clears last years information and makes the worksheet
available for a new year. I want the Macro to increase last year's balance
date by one year. How do you get the Macro to increase the date value of
the
active cell by one year?

Bob,

How would this work in a general Excel cell to increase the year & month by
12 months

"Bob Phillips" wrote:

With Activecell
.Value = Date(Year(.Value)+1,Month(.Value),Day(.Value))
End With

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"pkeegs" wrote in message
...
I have a macro which clears last years information and makes the worksheet
available for a new year. I want the Macro to increase last year's balance
date by one year. How do you get the Macro to increase the date value of
the
active cell by one year?

how 'bout

=today()+365

or

=A3+365
(because of 2007's leap year, at this point you have to make it +366).

hth
susan



On Jan 23, 2:08*pm, John wrote:
Bob,

How would this work in a general Excel cell to increase the year & month by
12 months



"Bob Phillips" wrote:
With Activecell
* * *.Value = Date(Year(.Value)+1,Month(.Value),Day(.Value))
End With

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"pkeegs" wrote in message
...
I have a macro which clears last years information and makes the worksheet
available for a new year. I want the Macro to increase last year's balance
date by one year. How do you get the Macro to increase the date value of
the
active cell by one year?- Hide quoted text -

- Show quoted text -

John,

What this code is doing is setting the value of the active cell (.Value = )
to the current value of the active cell adding one year
(Date(Year(.Value)+1,Month(.Value),Day(.Value))).

Although, if I'm not mistaken, it should be
DateSerial(Year(.Value)+1,Month(.Value),Day(.Value )) in VBA....Date(y,m,d)
in XL.

Lookup DateSerial in the VBA Help. DateSerial() returns a date serial
number and requires 3 arguments: Year, Month & Day in that order.

DateSerial(Year,Month,Day)
DateSerial(2008,1,23) 'Today's date

"ActiveCell.Value = DateSerial(2008,1,23)" would set the value of the active
cell to today's date.

Let's say we store today's date in a variable:

dim pdteToday as date
pdteToday = DateSerial(2008,1,23)

now using the variable, the DateSerial() function, and the Year(), Month(),
& Day() functions, we can set the value of the active cell to 1 year from
today:

ActiveCell.Value = DateSerial(Year(pdteToday) + 1, Month(pdteToday),
Day(pdteToday))

"Year(pdteToday)" extracts the year from pdteToday (2008),
"Month(pdteToday)" extracts the month (1), and "Day(pdteToday)" extracts the
day (23).......so:

ActiveCell.Value = DateSerial(2008 + 1, 1, 23)

Then:

ActiveCell.Value = DateSerial(2009, 1, 23)

Then

ActiveCell.Value = #1/23/2009# 'USA date format

Which would set the active cell to a year from today.

Now lets say that there is already a date in the active cell. I'm going to
use the existing date that is already there and add a year to it. Using the
previous code, I would replace "pdteToday" with "ActiveCell.Value":

ActiveCell.Value = DateSerial(Year(ActiveCell.Value) + 1,
Month(ActiveCell.Value), Day(ActiveCell.Value))

....what is essentially what Bob did, he just simplified it with "With
ActiveCell" and "End With"

HTH,

Conan



"John" wrote in message
...
Bob,

How would this work in a general Excel cell to increase the year & month
by
12 months

"Bob Phillips" wrote:

With Activecell
.Value = Date(Year(.Value)+1,Month(.Value),Day(.Value))
End With

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"pkeegs" wrote in message
...
I have a macro which clears last years information and makes the
worksheet
available for a new year. I want the Macro to increase last year's
balance
date by one year. How do you get the Macro to increase the date value
of
the
active cell by one year?