Example 2: This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure after it runs for a specific number of times.

The procedure should be entered in a Standard Module (select Insert>Module, in VBE code window).

Public eTime As Date
Dim count As Integer

__________________________

Sub CellValueAutoIncr2()
‘This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure after it runs for a specific number of times.

eTime = Now + TimeValue(“00:00:03”)
Application.OnTime eTime, “CellValueAutoIncr2”, , True
Cells(1, 1).Value = Cells(1, 1).Value + 5
count = count + 1

‘stop the procedure after it runs for 5 times:

If count = 5 Then

Application.OnTime eTime, “CellValueAutoIncr2”, , False

count = 0

End If

End Sub