Automatic increment of onTime

[Rob]

Hi,
Please will someone tell me (if possible) how to automatically increment the
times in the following example. I have got 370 times to enter in one minute
increments.

Sub StartTime()
Application.OnTime TimeValue("17:28:00"), "CopyVolume1"
Application.OnTime TimeValue("17:29:00"), "CopyVolume2"
Application.OnTime TimeValue("17:30:00"), "CopyVolume3"
End Sub

Thanks.

 

[OssieMac]

Hi Rob,

I have assumed that your start time is 17:28:00 so I have changed the
minutes to 27 to allow the first iteration to add 1 to become 28.

Also note that a space and underscore at the end of a line is a line break
in an other wise single line of code.

Dim i As Long

For i = 1 To 370
Application.OnTime _
TimeValue("17:" & 27 + i & ":00"), _
"CopyVolume" & i
Next i

 

[OssieMac]

Hi again Rob,

Disregard that answer; it won't work after 59 mins. I will have another look
at it.

 

[OssieMac]

Hi yet again Rob,

My apologies for the previous version. Was not thinking clearly. The
following method of including the date should handle going past midnight
also. However, the code is untested in conjunction with the OnTime function.
Start time can be real first time because not incremented until after first
iteration.

Sub test()

Dim i As Long
Dim timeStart As Date
Dim timeIncrement As Date

timeStart = Date + TimeValue("17:28:00")
timeIncrement = TimeValue("00:01:00")

For i = 1 To 370
Application.OnTime timeStart, "CopyVolume" & i
timeStart = timeStart + timeIncrement
Next i

End Sub

 

[Rob]

Thanks OssieMac.