How to run the same macro with 24 pre-defined intervals.

[ThrottleWorks]

Hi,

I need to run a macro for 24 times within 8 hours of time.

the gap between intervals is not uniform, sometimes it is 30 minutes, sometimes 15 minutes.

But all these intervals are pre-defined.

Also, for example if I am starting the macro at 6:00 in the evening today, 8 hours will be completed tomorrow 4:00 in the morning.

In this case the date will get changed, I am starting on 09-12-2015 and ending the process on 10-12-2015.

Can anyone please guide me how to proceed, what function should I use to check the timings.

I have never done such task before, can anyone please help me in this.

 

[Hui]

If you start at 6pm today and add 8Hrs, that will finish at 2am, not 4am tomorrow

Can you post a sample file showing what output you need, as it may be possible to do that with Formula instead of a macro

 

[ThrottleWorks]

Hi @Hui sir, thanks a lot for the help. Yes, my mistake, it will end at 2:00 AM not 4:00 AM.

showing what output you need

Sir, do you mean, what I am trying to achieve with macro. The macro takes data from one input file, process it, populate in different tabs of the final report based on the rules.

This is a bit lengthy procedure and needs to be done by macro only.
The problem is, we need to repeat same task 24 times in a day with this macro.

So instead of manually running the macro 24 times, we want macro to get triggered at pre-defined times.

Or, do you mean something else sir, sorry for confusing you.

Good night.

 

[Chihiro]

You can use Application.Ontime to run Macro on Schedule.

Assuming Sheet1!A1 contains start time.
A2 should have interval that should elapse before code runs for 2nd time.

Note that TimeValue only accepts string so ".Text" is used.

Just a quick sample.

Sub Schedule()
Dim sTime, sTime2, sTime3 As Date

sTime = DateValue(Date) + TimeValue(Sheet1.Cells(1, 1).Text)
sTime2 = sTime + TimeValue(Sheet1.Cells(2, 1).Text)
sTime3 = sTime2 + TimeValue(Sheet1.Cells(3, 1).Text)

'First run
    Application.OnTime sTime, "My_Macro"
'Second Run
    Application.OnTime sTime2, "My_Macro"
'So on so forth
End Sub

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help.
I am going through your code. Will revert with details, doubt.

Good night.

PS - FYI, screenshot of time intervals, this is not betweent today evening and tomorow early morning. I am trying easy one first.

 

[ThrottleWorks]

Hi @Chihiro sir, it's working perfect. I tried on a dummy data. Seems to be running great.

Could you check the attached file if possible, good night.

 

[Chihiro]

Hahaha. Thanks. Glad it worked.

Just a note as DateValue(Date) is only evaluated when the code run initially, it will not wrap to next day. You can work around it by using something like my example or just storing specific date along with Time in cell(s).