How to tell a Macro to do nothing for a set period of time?

[Swarnendu]

I want my macro to delay for 4 minutes before going to the next line after executing a line, but I don't want the whole worksheet to Pause or Sleep during that period.

Here's my code:

Sub MntCalculate()
If TimeValue(Now) > TimeValue("08:45:00") And TimeValue(Now) < TimeValue("09:00:00") Then
Range("M1:CS218").ClearContents
Application.OnTime Now + TimeValue("00:00:01"), "Sheet7.MntCalculate"
Else
If TimeValue(Now) > TimeValue("08:59:59") And TimeValue(Now) < TimeValue("16:00:01") Then
Application.Calculation = xlCalculationAutomatic
ActiveWorkbook.RefreshAll
LC = Application.Max(11, Cells(2, Columns.Count).End(xlToLeft).Column + 1)
For i = 2 To 218
Cells(1, LC) = Format(Now(), "hh:mm:ss")
' I wan't the Macro to wait for 4 minutes here....
Cells(i, LC) = Range("J" & i)
Next i
Application.OnTime Now + TimeValue("00:01:00"), "Sheet7.MntCalculate"
Else
Application.OnTime Now + TimeValue("00:14:00"), "Sheet7.MntCalculate"
End If
End If
End Sub




Thanks

Swarnendu

[Swarnendu]

Ok, I think I have found a solution.

The problem was, my RefreshAll command was to copy data from the web in one location in a 5 minutes' interval, and my For-Next loop was to then copy that data to another location. But these For-Next lines were being executed even before copying from the web was complete (slow connection).

Firing that RefreshAll in one minute's interval from another Macro, and delaying the current Macro by 10 seconds, and then running it at it's usual 5 minutes' interval seems to have done the trick.

Thanks anyway,


Swarnendu

[mdmackillop]

Are you sure? Pausing in the middle of a loop will take 4 x 217 minutes (15.5 hours) to run.:crying:

[Swarnendu]

I did notice that (after already :pstarting this forum) ....

Now I am running another macro simultaniously.