Update specific cell at a specific time
"CynthiaL" wrote:
Here is my continuous clock macro. My clock is in C4.
My information is in D4 and I want the information to
"snapshot" into E4. Basically what we are trying to do
is for example take a "snapshot" of D4 at let's say
9 am into E4 then every 5 minutes "snapshot D4 into E5
and so on.
Note some additional changes and comments.
Dim SchedRecalc As Date
Sub Recalc()
Range("C3:C4").Clear
Range("C3").NumberFormat = "dd-mmm-yy"
Range("C4").NumberFormat = "hh:mm:ss AM/PM"
Range("C4").Formula = "=C3"
Call SetTime
End Sub
Sub SetTime()
Dim t As Date
' *** update clock time (C4) every 1 sec.
' *** also update clock date (C3) in case
' *** run time spans midnight
t = Now
Range("C3") = t
' *** no need to treat 9:00 AM as a special case.
' *** it is one of "every 5 minutes" after midnight
If Minute(t) Mod 5 = 0 Then Range("E4") = Range("D4")
SchedRecalc = t + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, _
Procedu="Recalc", Schedule:=False
End Sub
|