Originally Posted by joeu2004[_2_]

"CynthiaL" wrote:
One more thing and I think I'm there. I need to take a snapsnot
and every five minutes put the new number in the cell
below the last one. Is that possible?

There are at least two ways to do it. I think the following is more
reliable. Also see notes below.

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
If Range("E4") = "" then
' first snapshot
Range("E4") = Range("D4") _
Else
' subsequent snapshots
Range("E4").End(xlDown).Offset(1) = Range("D4")
End If
End If
SchedRecalc = t + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub

-----

FYI, another way:

Sub SetTime()
Static cnt As Long
' ....
If Minute(t) Mod 5 = 0 Then
Range("E4").Offset(cnt) = Range("D4")
cnt = cnt + 1
End If

That is unreliable because cnt is reset to zero each time VBA is reset.

-----

Finally, original you said: ``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``.

I purposely ignored the 9am requirement, saying that "every 5 minutes"
includes 9am.

However, if you start run Recalc (to start the recurring events) before 9am,
my implementation will start collecting snapshots before 9am. With the
latest change, that might result in a lot of empty cells in E4 and below, or
a lot of old data from yesterday, for example.

If you want to wait until 9am before collecting samples, change the "If
Minute..." statement to:

If t = #9:00# And Minute(t) Mod 5 = 0 Then

Note that VBA will change the appearance of the time constant #9:00#.