Excel Date Incrementing Woes

[TroyBoy]

Hi,

I've been reading through the archives, and have run into ~similar~ problems, but nothing quite like this. I would like to increment a date by one month by clicking a button. I have done the(in a sub):

Range("A2").Value=Range("A2").Value +1

which increments by 1 day.

Then I have tried(in a sub):

Range("A2").Value=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))

Which gives me a syntax error of ) expected after YEAR.

What I am trying to do overall is click a button, the cell A2 will increment forward by one month, cells B4 through AF4 will increment forward by one month, and cells B5 through AF5 will change days (as in days of the week) with the change of the month.

Could anyone help me with this?

Thanks,

Troy

[Tom Schreiner]

Try this Troy...

Range("A2") = DateAdd("m", 1, Range("A2"))

If any problems, use this:

Range("A2").Value = DateAdd("m", 1, Range("A2").Value)

Tom

[TroyBoy]

WOW!

Free (Virtual)Beer for you Tom!

Thanks Muy!

Troy

[TroyBoy]

Hmmmm... well, it took care of cell A2... now for Range("B4:AF4"). I get a type mismatch error if I substitute Range("B4:AF4") in the formula. I ~think~ I've made all of the obvious mistakes in the past hour or so:

Range("B4:AF4") = DateAdd("m", 1, Range("A2"))

This ended up incrementing the B4:AF4 range by two years

Range("B4:AF4") = Range("A2"

This ended up making all dates the same as A2

Let's see... what else. Oh, I tried the Union thing:

Dim r1 as range, r2 as range, myrange as range

set r1 = ("A2")
set r2 = ("B4:AF4")
myrange = Union(r1,r2)


myrange=DateAdd("m", 1, Range("A2"))

Would the union thing work if I put a date in a different cell and ref'd that?

myrange=DateAdd("m", 1, Range("z1"))

Awwww heck... back to work!

Thanks,
Troy

[Tom Schreiner]

What are you trying to do in B4:AF4?
Do all of the cells in this range = A2?
or
Are you trying to increment the date by one month in each of these cells, each having a different month?

Tom

[TroyBoy]

I'm trying to increment cell A2 by month and increment cells B4:AF4 by month at the same time... here's the view:

January-02 <- cell A2

1/1/02 1/2/02 1/3/02 etc
B4 C4 D4 etc
Tu We Th etc
B5 C5 D5 etc

So when I increment A2 by one month, cells B4:AF4 and B5:AF5 all increment by one month. It's all part of a scheduling spreadsheet, where the people and the rest of the info stays the same, but the dates and days change.

Thanks for your help

Troy

[Tom Schreiner]

Sorry about the sporadic replies...
I'm at work.

Anyway, play around with the following...

Sub IncrementDate()

    'increments the current date in Cell A2

    Range("A2") = DateAdd("m", 1, Range("A2"))

    'uses the above date with the first day of the month

    'forced into Cell B4

    Range("B4") = Month(Range("A2")) & "/01/" & _

    Year(Range("A2"))

    'places a formula in C4 which adds one day to

    'Cell C4's value

    Range("C4").Formula = "=B4+1"

    'fills the formula across to AF4

    Range("C4:AF4").FillRight

End Sub

Tom

[TroyBoy]

Thanks Tom, I think that did it just dandy

I plugged it into a spinner control - modified it to work in both directions, and it's outstanding!

BTW Just got off work myself... time for some shut eye. Thanks again for your outstanding help

Troy

[TroyBoy]

LOL... It ran great until I e-mailed it to myself at home... Home.Minerva is running Excel 2000 with the updates, Work.PieceOfCrap is running Excel 97. Anyways, I sent it home, opened an played with it a bit, everything was cool, then I sent it back to me at work... Now I'm getting a run time Error #1004 FillRight Method of Range class failed. Pretty weird. I'm thinking it's an Excel thing... either that, or I should sell the computer and buy a yo-yo.

Peace!

Troy

[sykes]

Hi Troyboy
I've been using dates quite a bit recently, and think that the EDATE function may solve your problem quite easily.
I'm running 0ffice 2k, and you have to have the Analysis tool pack addin checked.

If you want to increase A2 by 6 months, into A3, just enter into A3 =EDATE(A2,6)and Bob's your uncle.

If you want to increase A2 by 6 months and one day, into A3, just enter into A3 =EDATE(A2,6)+1 and Bob's your auntie.

You'll have to custom format the cells appropriately of course.

Hope this isn't too simple, and that I've completely misread the complexity of the problem. I am after all only a "new boy" to the board.

Kindest

Sykes



[ This Message was edited by: sykes on 2002-05-19 13:00 ]