We help IT Professionals succeed at work.

Excel Timer

cpatte7372
cpatte7372 used Ask the Experts™
on
Hello Experts,

I'm not sure how to phrase this request but I hope you understand.

Can someone tell me if its possible to have Excel move the value of a cell to another cell in a specified time.

Let me explain

Lets say a calculation has been completed in cell K3 I would like the value within K3 to move to L3 in 15min intervals. So, lets say the value of the cell in K3 was 10 at 15:15pm I would like that value to move to L3. And lets say the time now is 15:30pm and the value of the cell in K3 is now 23, I would like that value to replace the old value 10 in cell L3

I bet you excel experts haven't had a question like that before :-)

Can you please let me know if it possible, and if it is how do I make it happen?


Cheers


Carlton
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Juan OcasioApplication Developer

Commented:
Indeed that is a strange one.  Is the Excel sheet always open?  If so, you may be able to pull this off writing a macro.

Author

Commented:
Hi jocasio123,

Yeah, I thought this would tickle some of you experts.

Yes the spreadsheet is always open. However, I'm not sure how a macro would solve this?

Cheers
Try adding this macro to a general module


Public Sub timer15()
Sheets("sheet1").Range("l3").Value = Sheets("sheet1").Range("k3").Value
Application.OnTime Now + 15 / 24 / 60, timer15
End Sub


Make sure that the sheet name is correct and then run the macro once every session. You might like to run it through the worksheet_open event.

I have not tested it so come back if you have a problem

Author

Commented:
ssaqibh, thanks for responding.

Can you explain where you say 'run the macro every session'? Are you saying the macro won't run automatically?

Cheers

Carlton

Every time you "Open the file" you would have to run the macro once. Thereafter the macro should take care of it every 15 minutes.

Author

Commented:
ssaqibh

I was putting your code to the test and I got the following error message:

Compile Error:

Expected Function or Variable

And then highlighted

timer15

Any thoughts?

Cheers

Author

Commented:
ssaqibh,

I figured it out. However, can you please explain the following line:

Application.OnTime Now + 15 / 24 / 60, Timer = 15

I understand the time is 15, but I don't get Application.OnTime Now + 15 / 24 / 60.

Cheers

Now is the present date/time
15/24/60 is the number of days from now.

timer=15 should be timer15 which is the name of the sub

Author

Commented:
ssaqibh

It didn't work.

When the macro to set to run I get an error message. The error message is very long so I did a snapshot of it, please see attached.

BTW,

I tweaked the macro to work with my sheet as follows:


Public Sub timer15()
Sheets("mini sized DOW").Range("BM3").Value = Sheets("mini sized DOW").Range("BL3").Value
Application.OnTime Now + 15 / 24 / 60, Timer = 15
End Sub






EE-ErrorMessage.doc

Author

Commented:
Hi ssaqibh,

I'm still unsure by "15/24/60 is the number of days from now".

Cheers

15/24/60 = 0.0104166666666667 days which is equivalent to 15 minutes.

As I said before timer=15 should be timer15 which is the macro name

Author

Commented:
ssaqibh,

If I change Timer = 15 to timer15, I get the error message:

Compile Error:

Expected Function or Variable

And then highlighted

timer15

So I changed it to Timer = 15 and the error message disappeared.

However, I keep on getting the message that I uploaded as a .doc file.

Author

Commented:
So, with the timer changed to the following I get the compile error message:

Public Sub timer15()
Sheets("mini sized DOW").Range("BM3").Value = Sheets("mini sized DOW").Range("BL3").Value
Application.OnTime Now + 15 / 24 / 60, timer15
End Sub
Many apologies. It should have been

Application.OnTime Now + 15 / 24 / 60, "timer15"

instead of

Application.OnTime Now + 15 / 24 / 60, timer15

Author

Commented:
ssaqibh, no worries mate.

Going to test it out now...

Author

Commented:
Counting down.

Author

Commented:
So in theory this should move the value from BL3 to BM3 at 18:45 GMT, correct?

Cheers
Rather it would be 15 minutes after the first time the command is run.

You would require a slightly more complex formula to make it every quarter on the clock.

Author

Commented:
Oh I see.

Would it be extremely more complicated? And it would it be very intense on cpu resources?

To be honest, it would be ideal if the formula would change from the clock on PC

Cheers

Author

Commented:
Just so you know it worked! Brlliant.

Be great if it could work by the clock on the PC

Author

Commented:
ssaqibh

How did you work this out?

15/24/60 = 0.0104166666666667 days which is equivalent to 15 minutes???
Try this formula, don't ask me how.

Public Sub timer15()
MsgBox ("timer run at" & Now)
Sheets("mini sized DOW").Range("BM3").Value = Sheets("mini sized DOW").Range("BL3").Value
Application.OnTime CVDate((Int(Now * 4 * 24) + 1) / 24 / 4), "timer15"
End Sub
1 means 1 day (24 hours)
1/2 0.5 means half a day (12 hours)
1/24 means 1 hour
1/24/60 means 1 minute
15/24/60 means 15 minutes

Author

Commented:
Ha ha,

Going to try formula now....

Author

Commented:
Count down.

Will let you know in 15 mins...

Fingers crossed

Author

Commented:
ssaqibh,

While I'm waiting for 15 min interval, thanks for explaining how you worked out the times. I was just wondering if you could show me how to change the new formula for two minutes? So that I don't have to wait 15mins for each test.

Cheers
The two 4s you can see are basically 60/15

for 2 mins try 60/2 or 30 instead of 4

Application.OnTime CVDate((Int(Now * (60 / 2) * 24) + 1) / 24 / (60 / 2)), "timer15"

Author

Commented:
Whoopee... it worked.

You deserve a gold star.

I just need to run a test with the two minutes.....
Maybe you need to run many more tests before accepting.

Author

Commented:
ssaqibh,

The formula appears to be working fine. Can you please show me how to add a range of cells? At the moment the formula works for BM3 &BL3. I would like to add another 31 rows. So the next two cell would be BM4 & BL4 all the way down to BM33 & BL33.

I thought it might have been something like

Sheets("mini sized DOW").Range("BM3:BM33").Value = Sheets("mini sized DOW").Range("BL3:BL33").Value

But I'm sure that isn't correct.

Cheers

Sheets("mini sized DOW").Range("BM3:BM33").Value = Sheets("mini sized DOW").Range("BL3:BL33").Value

should work

Author

Commented:
ssaqibh

Cheers dude.

Won't be able to fully test it until Monday when the markets open.

You've been great.

I have another question that I'm about to post. Its similar to the last request you helped me with.

Author

Commented:
Absolutely brilliant...