Do Until Loop query

[shumba]

Good Afternoon,


Please will someone advise me on the correct syntax for a Do Until Loop to a specific time. The procedure below produces a compile error.

Sub Phase2()
      Dim i  As  Integer
      'Application.ScreenUpdating = False
      'Workbooks("Data Collection Template Ver 2.xlsm").Activate
      
      Do Until Application.OnTime TimeValue("07:02:00"),
      Sheets("Data Collection and Compilation").Select
      Range("h2:h3006").Copy
      Selection.Offset(-1, 0).Value = Now
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
      Selection.Offset(1, 0).Select
      Selection.FormulaR1C1 = "=AVERAGE(R[-1]C[-5]:R[-1]C)"
      
      For i = 1 To 1502
      Selection.Offset(2, 0).Select
      Selection.FormulaR1C1 = "=AVERAGE(R[-1]C[-5]:R[-1]C)"
      Next i
      Selection.Offset(-3005, 1).Select
      Loop
  End Sub

Any help will be much appreciated.

Rob.

 

[Von Pookie]

This seems to work for me--

Change this:

Do Until Application.OnTime TimeValue("07:02:00"),

To this:

Do Until Format(Now, "hh:mm:ss") >= "07:02:00"

And give it a shot

 

[shumba]

Thanks for your help. I will give it a go as soon as I start work this morning.

Regards,

Rob.