Help, simple excel...

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
Quote from Don Bright:


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
More...
 
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 :D

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
 
Quote from Don Bright:

I have the emini's in a cell continually updating. I want another cell to "grab" the emini cell's value at a specific time of day.
More...


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.
 
Quote from Kevin Schmit:

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)
More...

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)
 
You must log in or register to reply here.
Back
Top