Automatic increment of onTime

R

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.
 
O

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
 
O

OssieMac

Hi again Rob,

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top