Multiple condtion For loop, dynamic named range

Hi,

This is my first play with VBA so apologise if the question is very easy and but I've been running into errors I cant resolve for the last couple of days.
(if someone can suggest a good debugging tutorial would also be appreciated).

I have a spreadsheet of building data energy use (15min intervals for many days, I've attached just two days). I'm trying to extract the time ranges where use is high/low or ramping up and down.

All I think I need is a for loop, which loops through a named range and returns the value of the adjacent time column for the first cell which exceeds a bound (in this case this is the variable "mid"), following this I want to continue looping through the range and returns the time value where the value of the range drops below the variable "mid".

My first problem is in this implementation loop, it gives an application/object definition error, which so far I can't resolve.

I've attached the spreadsheet with my current VBA code

Thanks for your time

Thanks rylo and AlphaFrog,

The dual conditions for transition points is perfect. Because I'm working with multiple days in the same file, I think stuck with the named range and offset method. To do this I used rylo's logic for the if conditions, and used the c.Row property to return what cell satisfied the conditions.

Please Login or Register  to view this content.

There's undoubtedly more efficient ways to do this, and if anyone wants to suggest those feel free.

Thank you