On 31 Oct 2004 13:56:58 GMT, Ron
wrote:

Thanks Guys,

Had a mess around and now got the timed procedure working, and taking
the time from the correct cell, thanks a bunch.

I've now hit the hurdle whereas I can't get it to run more than once.

I've tried an If Then then statement increasing a counter by one to
control the number of runs of the code.

I've also tried a Do until loop but it doesnt seem to want to do it
twice.

Here's the code as it is now, working once but not twice.

Sub Start()
Dim counter As Integer
counter = 0
Dim countTotal As Integer
countTotal = Sheets("test").Range("i2")
Do Until counter = countTotal
OnTimeTest
Exit Do
counter = counter + 1
Loop
End Sub

Lets assume that the Exit Do statement is put there just for testing
purpose (as it makes no sence to unconditionally exit the loop at that
point).
The result from this Start procedure would then be to have a
countTotal number of scheduled activations where the detailes are
left for the OnTimeTest to deal with. OK no problem so far.


Sub OnTimeTest()
Dim MyTime
Dim TimeCell2
TimeCell2 = Sheets("test").Range("k2")
MyTime = Format(TimeCell2, "hh:mm:ss")
Application.OnTime TimeValue(MyTime), "testProcedure"
End Sub

OK, now the problem starts. As this procedure is called a number of
times (by the Start loop) there will be a number of scheduled
activations of the testProcedure procedure.
However. All these activations will be on the same time, i.e. the
time found in cell K2 at the time of the execution of the above
mentioned loop. This must be different from what you really want.


Sub testProcedure()
Sheets("test").Select
Range("K2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub


OK, this procedure changes the value in K2 by shifting new values up.
But this is of absolutely no use since all the countTotal activations
are alreade scheduled to the time originally in K2.

So the effect of all this is just that (assuming the Exit Do is
removed) you will have a number of activations of the testProcedure
procedure at the (almost) same time, each shifting the K column
up one step.


I've tried the ,Schedule:= False on the end on the App.OnTime line but
to no avail.

Any thoughts?


Yes, try modifying the Start procedure to call Application.OnTime
with the DIFFERENT wanted activation times. These times could,
according to your original specification (although the F column seems
to be the K column now), be found in the K column.
The OnTimeTest you can skip and the "testProcedure" should only
do what you would like to happen at the specified times which is
probably something else than modifying the K column.
You might also want to change the loop exit criteria (from = to =)
if you would like to use the value 0 with expected result (i.e. no
activations).

With the above taken into account and without changing anything else
your code would the be like this:

Sub Start()
Dim counter As Integer
counter = 0
Dim countTotal As Integer
countTotal = Sheets("test").Range("i2")
Do Until counter = countTotal
Application.OnTime
TimeValue(Format(Sheets("test").Range("K2").Offset (counter, 0),
"hh:mm:ss")), "testProcedure"
counter = counter + 1
Loop
End Sub

Sub testProcedure()
MsgBox Format(Now() - Int(Now()), "hh:mm:ss")
' replace this with what you really want to do here
End Sub

Lars-Åke

Regards,

Ron