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
|