Updating Dates to Next Specific Weekday Automatically
 

Hi, I need to take a column (C) of dates, check it against a column (B)
that contains a weekday (either Tuesday or Thursday). I then need it to
check if that date in column C has already occured and then modify the
cell. This all I can do. This is the tricky part for me.

I need it to change the date in the cell to the same 'Tuesday' or
'Thursday' of the next month. So if the current date in C3 is the date
of the 2nd tuesday of August (and B3 is 'August') , and that date has
come and gone, I want it to change C3 to the 2nd tuesday of the next
month, September.

Any help and suggestions would be awesome. Thank you.

Eric
elraver AT yahoo DOT com

Updating Dates to Next Specific Weekday Automatically
 

Note, this is the current macro I have written. Right now, all it does
is turn all the cells from C5 to C24 to the current date if the date in
the cell has already passed.

What I /want/, is if the current date in the C4 cell (for example) is
the first Tuesday of the month (assuming that 'B4' = "Tuesday" rather
than "Thursday"), and that day has passed, it should then update the C4
cell to the next appropriate day of the following month.

Example
A | B | C
5 Eric | Tuesday | 8/3/2006

It should get from Column B that this is a Tuesday night student, then
it should see that 8/3/2006 has already passed. It will then realize
that 8/3/2006 was the first Tuesday of this month and thus make sure
that it updates the C5 cell to the first Tuesday of next month. Then...
well, update it. :)

Sub Auto_Open()
Dim dtmFormatedDate As Date
For Each c In Range("C5:C24")
For Each b In Range("B5:B24")
dtmFormatedDate = Format(c.Value, "YYYY,MM,DD")
If dtmFormatedDate < DateTime.Date Then
If b.Value = "Tuesday" Then
c.Value = Format(DateTime.Date, "MM") + "/" +
Format(DateTime.Date, "DD") + "/" + Format(DateTime.Date, "YYYY")
ElseIf b.Value = "Thursday" Then
c.Value = Format(DateTime.Date, "MM") + "/" +
Format(DateTime.Date, "DD") + "/" + Format(DateTime.Date, "YYYY")
End If
End If
Next b
Next c
End Sub
---

Thanks for any and all help!
Eric