OnTime Challenge

[ewagz]

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

[Tom Ogilvy]

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

[ewagz]

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

[Tom Ogilvy]

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

[ewagz]

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