| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OnTime Challenge
I would like to run a macro using the ontime method that would run the macro
every 30 minutes. The spin is that I need the macro to run not when the run button is pressed, but rather wait until the half hour (top and bottom of the hour) with a delay of 10 min past that half. Example: Current time: 3:52 PM Start the macro The macro will wait until 4:00 PM The macro will delay the run until cell ("A3") IE. 10mins Macro runs at 4:10 PM I would appreciate any help with this problem! -- EW - Analyst |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
OnTime Challenge
In the macro fired when the button is clicked, schedule it to run on the next
half hour. Then in the macro that runs, schedule the next running of the macro. See Chip Pearson's site for an example http://www.cpearson.com/excel/ontime.htm -- Regards, Tom Ogilvy "ewagz" wrote: I would like to run a macro using the ontime method that would run the macro every 30 minutes. The spin is that I need the macro to run not when the run button is pressed, but rather wait until the half hour (top and bottom of the hour) with a delay of 10 min past that half. Example: Current time: 3:52 PM Start the macro The macro will wait until 4:00 PM The macro will delay the run until cell ("A3") IE. 10mins Macro runs at 4:10 PM I would appreciate any help with this problem! -- EW - Analyst |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
OnTime Challenge
Sorry,
How do I make a macro run on the next half hour? -- EW - Analyst "Tom Ogilvy" wrote: In the macro fired when the button is clicked, schedule it to run on the next half hour. Then in the macro that runs, schedule the next running of the macro. See Chip Pearson's site for an example http://www.cpearson.com/excel/ontime.htm -- Regards, Tom Ogilvy "ewagz" wrote: I would like to run a macro using the ontime method that would run the macro every 30 minutes. The spin is that I need the macro to run not when the run button is pressed, but rather wait until the half hour (top and bottom of the hour) with a delay of 10 min past that half. Example: Current time: 3:52 PM Start the macro The macro will wait until 4:00 PM The macro will delay the run until cell ("A3") IE. 10mins Macro runs at 4:10 PM I would appreciate any help with this problem! -- EW - Analyst |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
OnTime Challenge
One straight forward way:
Public Function NextHalf() hr = Hour(Now) mn = Minute(Now) If mn = 30 Or mn = 0 Then NextHalf = Now ElseIf mn < 30 Then NextHalf = Date + TimeSerial(hr, 30, 0) Else NextHalf = Date + TimeSerial(hr + 1, 0, 0) End If End Function -- Regards, Tom Ogilvy "ewagz" wrote: Sorry, How do I make a macro run on the next half hour? -- EW - Analyst "Tom Ogilvy" wrote: In the macro fired when the button is clicked, schedule it to run on the next half hour. Then in the macro that runs, schedule the next running of the macro. See Chip Pearson's site for an example http://www.cpearson.com/excel/ontime.htm -- Regards, Tom Ogilvy "ewagz" wrote: I would like to run a macro using the ontime method that would run the macro every 30 minutes. The spin is that I need the macro to run not when the run button is pressed, but rather wait until the half hour (top and bottom of the hour) with a delay of 10 min past that half. Example: Current time: 3:52 PM Start the macro The macro will wait until 4:00 PM The macro will delay the run until cell ("A3") IE. 10mins Macro runs at 4:10 PM I would appreciate any help with this problem! -- EW - Analyst |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
OnTime Challenge
Thanks for the help, that should do it!
-- EW - Analyst "Tom Ogilvy" wrote: One straight forward way: Public Function NextHalf() hr = Hour(Now) mn = Minute(Now) If mn = 30 Or mn = 0 Then NextHalf = Now ElseIf mn < 30 Then NextHalf = Date + TimeSerial(hr, 30, 0) Else NextHalf = Date + TimeSerial(hr + 1, 0, 0) End If End Function -- Regards, Tom Ogilvy "ewagz" wrote: Sorry, How do I make a macro run on the next half hour? -- EW - Analyst "Tom Ogilvy" wrote: In the macro fired when the button is clicked, schedule it to run on the next half hour. Then in the macro that runs, schedule the next running of the macro. See Chip Pearson's site for an example http://www.cpearson.com/excel/ontime.htm -- Regards, Tom Ogilvy "ewagz" wrote: I would like to run a macro using the ontime method that would run the macro every 30 minutes. The spin is that I need the macro to run not when the run button is pressed, but rather wait until the half hour (top and bottom of the hour) with a delay of 10 min past that half. Example: Current time: 3:52 PM Start the macro The macro will wait until 4:00 PM The macro will delay the run until cell ("A3") IE. 10mins Macro runs at 4:10 PM I would appreciate any help with this problem! -- EW - Analyst |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find %ontime & SUMIF ontime ie: find matching sets within Range... | Excel Worksheet Functions | |||
| OnTime VB | Excel Discussion (Misc queries) | |||
| OnTime VB | Excel Worksheet Functions | |||
| OnTime | Excel Programming | |||
| OnTime...Please Help | Excel Programming | |||
Linear Mode
