Swarnendu
07-20-2017, 01:00 PM
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
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