PDA

View Full Version : OnTime reschedule based on cell date days from today



b.hill
08-18-2012, 04:19 PM
The code below is supposed to look at the date in cell "B2" and determine when to reschedule the macro based on how many days the date in cell "B2 is from today. I can't get the code to perform anything other than the "Case 0 To 0" ontime option regardless of the date in cell "B2". I added the line of code ActiveSheet.Range("c3") = iDaysDiff to find out that "iDaysDiff" always results in "0" no matter what the date is in cell "B2". This explains why the "Case 0 To 0" ontime option is always performed, but I would like the "iDaysDiff" to result in the number of days from the date in "B2" to today instead of always resulting in "0". The date in cell "B2" is in the month/day/year date format.


Sub Macro1()

'Code
'Code
'Code

Dim dt As Date
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim iDaysDiff As Integer
dt = Sheet1.Cells(2, 2)
iDay = Day(Now)
iMonth = Month(Now)
iYear = Year(Now)
iDaysDiff = dt - DateSerial(iYear, iMonth, iDay)
ActiveSheet.Range("c3") = iDaysDiff
Select Case iDaysDiff
Case 0 To 0
Application.OnTime Now + TimeValue("00:00:30"), "Macro1"
Case 1 To 2
Application.OnTime Now + TimeValue("00:01:00"), "Macro1"
End Select


End Sub

Bob Phillips
08-18-2012, 04:30 PM
Sub Macro1()

'Code
'Code
'Code

Dim dt As Date
Dim iDaysDiff As Integer

dt = Sheet1.Cells(2, 2)
iDaysDiff = dt - Date
ActiveSheet.Range("c3") = iDaysDiff
Select Case iDaysDiff
Case 0
Application.OnTime Now + TimeValue("00:00:30"), "Macro1"
Case 1 To 2
Application.OnTime Now + TimeValue("00:01:00"), "Macro1"
End Select
End Sub