Update specific cell at a specific time

[CynthiaL]

I have a macro for clock running in Excel. Data in one cell i
constantly changing. At a specific time, I want that cell at that tim
be copied into another cell. The value in that cell is constantl
changing. How do I do this?

For example, stock prices, they are constantly changing on m
spreadsheet
and at 10:30 I want the price to be copied into a specific cell.

Thanks for your help in advanced!
Cynthi

 

[isabelle]

hi Cynthia,

you can use Application .OnTime on a Workbook_Open events

Application .OnTime(EarliestTime, Procedure, LatestTime, Schedule)

http://msdn.microsoft.com/en-us/library/office/ff196165.aspx

isabelle

Le 2013-08-16 13:56, CynthiaL a écrit :

 

[joeu2004]

I have a macro for clock running in Excel. Data in one
cell is constantly changing. At a specific time, I want
that cell at that time be copied into another cell. The
value in that cell is constantly changing. How do I do this?
For example, stock prices, they are constantly changing on
my spreadsheet and at 10:30 I want the price to be copied
into a specific cell.

Insufficient information for a simple answer. Provide more details about
existing VBA code. Better: post the URL of an example Excel file that you
uploaded to a file-sharing website. See a list of some free file-sharing
websites in footnote [1]; or use your own.

If the "marco for clock running in Excel" is VBA code that runs every second
or minute, for example, you might simply add the following code to it:

If Time = TimeSerial(10,30,0) Then Range("A1") = Range("B1")

Change that to TimeSerial(22,30,0) if you mean 10:30 PM (!).

Alternatively, you might add the following code __somewhere__. (Exactly
where depends on those "more details" that you omitted.)

Application.OnTime TimeSerial(10,30,0),"doit"

and separately:

Sub doit
Range("A1") = Range("B1")
' the following only if you expect to keep the Excel
' file open for more than one day
Application.OnTime TimeSerial(10,30,0),"doit"
End if

Caveat: The first OnTime statement schedules the event macro ("doit") to
run the __next__ time it is 10:30 on some day. If it is already past 10:30
today, "doit" will run tomorrow. If you would prefer "doit" to run today if
it is already past 10:30, change __first__ OnTime statement to:

Application.OnTime Date+TimeSerial(10,30,0),"doit"


-----
[1] Some free file-sharing websites:

Box.Net: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

 

[CynthiaL]

Here is my continuous clock macro. My clock is in C4. My information i
in D4 and I want the information to "snapshot" into E4. Basically wha
we are trying to do is for example take a "snapshot" of D4 at let's sa
9 am into E4 then every 5 minutes "snapshot D4 into E5 and so on.

D4 is a DDE link so I can't upload the file. I hope this makes sense.
have been working on Excel since the first version but most of my inf
over the years has been pretty easy and not in the need of extensiv
macros.

I hope you can help me with this and if you can think of a better way t
do it, please let me know. Thanks!



Dim SchedRecalc As Date
Sub Recalc()
Range("C3").Value = Format(Now, "dd-mmm-yy")
Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")

Call SetTime
End Sub


Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"

End Sub


Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc"
Schedule:=False
End Sub

 

[joeu2004]

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, _
Procedure:="Recalc", Schedule:=False
End Sub

 

[CynthiaL]

This is great. Thanks! One more thing and I think I'm there. I need t
take a snapsnot and every five minutes put the new number in the cel
below the last one. Is that possible?

Thanks,
Cynthia

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 Su

 

[joeu2004]

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#.