View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
[email protected] elraver@gmail.com is offline
external usenet poster
 
Posts: 2
Default 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