Avatar of cpatte7372
cpatte7372
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Excel Timer

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
Microsoft ExcelSpreadsheets

Avatar of undefined
Last Comment
cpatte7372

8/22/2022 - Mon
Juan Ocasio

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.
cpatte7372

ASKER
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
Saqib Husain

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
cpatte7372

ASKER
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

Saqib Husain

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.
cpatte7372

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cpatte7372

ASKER
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

Saqib Husain

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
cpatte7372

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
cpatte7372

ASKER
Hi ssaqibh,

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

Cheers

Saqib Husain

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
cpatte7372

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cpatte7372

ASKER
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
Saqib Husain

Many apologies. It should have been

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

instead of

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

ASKER
ssaqibh, no worries mate.

Going to test it out now...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
cpatte7372

ASKER
Counting down.

cpatte7372

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

Cheers
Saqib Husain

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cpatte7372

ASKER
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
cpatte7372

ASKER
Just so you know it worked! Brlliant.

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

ASKER
ssaqibh

How did you work this out?

15/24/60 = 0.0104166666666667 days which is equivalent to 15 minutes???
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Saqib Husain

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
cpatte7372

ASKER
Ha ha,

Going to try formula now....
cpatte7372

ASKER
Count down.

Will let you know in 15 mins...

Fingers crossed
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cpatte7372

ASKER
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
Saqib Husain

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"
cpatte7372

ASKER
Whoopee... it worked.

You deserve a gold star.

I just need to run a test with the two minutes.....
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Saqib Husain

Maybe you need to run many more tests before accepting.
cpatte7372

ASKER
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

Saqib Husain

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

should work
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cpatte7372

ASKER
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.
cpatte7372

ASKER
Absolutely brilliant...