Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 5
Talking Update specific cell at a specific time

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.

Thanks for your help in advanced!
Cynthia
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default Update specific cell at a specific time

hi Cynthia,

you can use Application .OnTime on a Workbook_Open events

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

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

isabelle

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

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.

Thanks for your help in advanced!
Cynthia




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 829
Default Update specific cell at a specific time

"CynthiaL" wrote:
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
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com

  #4   Report Post  
Junior Member
 
Posts: 5
Red face

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.

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

I hope you can help me with this and if you can think of a better way to 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, Procedu="Recalc", Schedule:=False
End Sub





Quote:
Originally Posted by CynthiaL View Post
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.

Thanks for your help in advanced!
Cynthia
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 829
Default 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



  #6   Report Post  
Junior Member
 
Posts: 5
Red face

This is great. Thanks! 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?

Thanks,
Cynthia

Quote:
Originally Posted by joeu2004[_2_] View Post
"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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 829
Default Update specific cell at a specific time

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

  #8   Report Post  
Junior Member
 
Posts: 5
Smile

Quote:
Originally Posted by joeu2004[_2_] View Post
"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#.

I know it has been a while and I thank you for all of your help but I didn't get a chance to finish this calc.

I can get it to work from D4 to E4 but if D4 changes I want it to put that changed number in the next row down which would be E5. So every five minutes I want it to put that new number in the next row down. Is this possible? Thanks!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell Update only at a specific time. Scott520 Excel Discussion (Misc queries) 4 August 8th 13 08:41 PM
Highlight a cell after update in a specific range Mark[_71_] Excel Programming 2 April 25th 13 09:34 AM
Send data from userform to specific cell on specific sheet? JennLee Excel Programming 10 March 10th 07 03:55 AM
Link from a specific Cell in Excel to a specific para. in Word CathyK Excel Worksheet Functions 0 August 10th 06 04:40 PM
Update a cell with a specific value related to a date. Jeff Excel Worksheet Functions 5 December 9th 05 07:14 PM


All times are GMT +1. The time now is 01:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"