Finding Time Ranges Given a Criteria

[MJW]

Hi All,

I need help writing vba code that will do the following:

I have a report that shows time intervals in column A and number of calls in column B. I need the start time and end time of the ranges of time where the calls exceed 30. So for the spreadsheet that i attached i would need the the macro to return 8:30 AM-11:00 AM, 12:30 PM-2:30 PM, and 3:30 PM-5:00 PM in three different cells. Since there is more than one range i would need the macro to be able to find multiple ranges and return results to multiple cells.

Any help would be greatly appreciated.

MJW

[jaslake]

Hi MJW

Based on your sample file, please demonstrate what the results should look like.

[MJW]

Hi John,

I have reattached the file with the way i would prefer the results to look like. However, if it makes it easier, start and end times can in separate cells. I'm just throwing the values into text strings.

Thank you for your help!

[jaslake]

Hi MJW

Try the attached. Let me know of issues.

[MJW]

Hi John,

This code works great 99% of the time. The only issues i'm having are when the first cell (B2) is part of the range the code is returning as the result. Instead of returning say 8:00 AM-9:00 AM it returns a blank in the first row of the results followed by the remaining correct results.

The other issue i'm having is when there are no valid results. It kicks back a runtime error 1004 and indicates an issue with the following line of the summary sub:

Please Login or Register  to view this content.

Could this be corrected just by adding an "on error resume next line above the beginning of the for statement?

Thanks again for your help!

MJW

[jaslake]

Hi MJW

I can duplicate this and have a fix for it

when there are no valid results. It kicks back a runtime error 1004

I'm unable to duplicate this

when the first cell (B2) is part of the range the code is returning as the result. Instead of returning say 8:00 AM-9:00 AM it returns a blank in the first row of the results followed by the remaining correct results.

Please post a sample file (with code) that demonstrates the issue. I'm sure we'll be able to deal with it.

[MJW]

I have attached an example of the second issue. It turns out that i was mistaken about the issue. Usually the results return the word Summary, have a blank cell below, and then have the results listed below that. In this example, it lists all options but when the 8:00 AM interval is part of the results it lists information as Summary, Result 1 (including 8am, Blank row, and then the rest of the results are listed. The example shows the issue after the macro has been run.

I have made minimal adjustments to the code for where i want the results returned. instead of column E have them listed starting in Cell B34...could this be part of the problem?

[jaslake]

Hi MJW

Try the code in the attached. I believe it solves this issue

when there are no valid results. It kicks back a runtime error 1004

and this issue

it lists information as Summary, Result 1 (including 8am, Blank row, and then the rest of the results are listed

I've also modified your code such that B34 is not hard coded. B34 has been redefined as

Please Login or Register  to view this content.

Let me know of issues.

[MJW]

Perfect! thank you so much for your help! This was the last step in a HUGE amount of automation for daily reporting that i've been working on. Thank you again!

[jaslake]

Hi MJW

You're welcome...glad it works for you. If this satisfies you need, please mark your Thread as "SOLVED".