Hey Raver, How often does the application.ontime function poll the system clock? I've never heard of that method before.... just the used the windows API or checking the value against the =now() function in the OnCalculate event. Also, FWIW I've always used: sheets("sheet1").range("A1").value = sheets("sheet1").range("A2").value ... for copying cell values in excel. It's a little more effecient, I believe, but doesn't really matter either way. Thanks, - The New Guy PS. Don, did you get my sample sheet? If so, you may want to test it against Raver's code, his may be more accurate.
I'm still working on' it all....combined a bit of several suggestions. I now have cell F4 aoutomatically updating the correct time by minutes. I have my eMini real time prices in cell D5. I'm still not seeing how I can place the D5 price at exactly 12:30 (or whatever time) into another cell. When F4=12:30 PM copy D4 to D8 or something like that. BTW, the last time I programmed anything was in Applesoft in 1981....LOL. This is why I let the young people apply my ideas to programs..... Thanks to everyone...if we can finish this up, let me know. Don
lol, applesoft might work for this, haven't tried... I really don't know of anyway to do it just in cell formulas. Generally it's pretty tough to copy and paste things in formulas in excel. The only way it might be possible is with a UDF, but I don't think you can do that either, in fact. You may have to delve into the world of VBA, but it's really not that bad. If you have any more questions, feel free to post them and I'll do my best to help. Thanks, - The New Guy
From the EXcel help site mentioned above. ------------------------------- Look at Chip Pearson's page on using OnTime http://www.cpearson.com/excel/ontime.htm You might also look at this on Stephen Bullen's site: LastChng.zip (12 Oct 1996, 5k, 14626 downloads) Here's the deal: You have a number of cells that are changing on a regular basis (e.g. DDE stock price feeds). At any point in time, you want to know which was the last cell that changed, what was its value before the change, and what is its value now. This worksheet does exactly that, using circular references. Full explanations included in cell notes in the sheet. http://www.oaltd.co.uk/Excel/Default.htm -- Regards, Tom Ogilvy ------------------------------------- Still requiring reading...I'm really hoping to just get a line of formula to stick into a cell, LOL. Thanks again... Don
Thenewguy is right, you should try to have a look into vba, it makes things so much easier. I use this code in my tradesheets and it works like a charm. I also got a macro that creates a timestamp when the macro pasted the data and it puts in the same time as the time I wanted the macro to copy paste the data. Thenewguy, I don't know how it exactly works or how its looks at the system clock etc. I know it works Good luck Don Raver
Don, Post the sheet, or a sample that's similar and I'll see what I can come with using a straight formulas. I prefer them over VBA or macros because they are cleaner. Some things require VBA, but you'd be surprised what formulas will do. I didn't respond earlier because I thought someone would have it by now. There are some great Excel guys here. Thanks ktm
Here's an example of visual basic that I recently used to copy a value at the same time each day: Sub Timecheck() Macro1 NextTick = Now + TimeValue("00:00:01") Application.OnTime NextTick, "Timecheck" End Sub Sub Macro1() 'the specified time to check the cell is in A1 If Time = Range("A1").Value Then 'the price to check is in A2 Range("A2").Copy 'copy the price at the specified time to A3 Range("A3").PasteSpecial End If End Sub
You can easily do it with just one extra intermediate cell. For example, to grab the value of cell c2 in cell f2: c2 is [dde emini link] d2 is =if(MOD(now(0),1)<time(12,0,0),c2,d2) e2 is =if(MOD(now(0),1)>=time(12,0,0),d2,0) Cell e2 will display zero before noon, the noon emini value at noon and later. Cell d2 stops changing at noon. Not that cell d2 is self-referencial. For this to work you have to go to tools/options/calculation and check the Iteration box.
That was a typo. Should read "to grab the value of cell c2 in cell e2" And I apparently can't post a less than symbol here. So here are the formulas again. c2 is [dde emini link] d2 is =if(MOD(now(0),1) [less than symbol] time(12,0,0),c2,d2) e2 is =if(MOD(now(0),1)>=time(12,0,0),d2,0)