Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
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:
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
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:
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
Quote:
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Update only at a specific time. | Excel Discussion (Misc queries) | |||
Highlight a cell after update in a specific range | Excel Programming | |||
Send data from userform to specific cell on specific sheet? | Excel Programming | |||
Link from a specific Cell in Excel to a specific para. in Word | Excel Worksheet Functions | |||
Update a cell with a specific value related to a date. | Excel Worksheet Functions |