Counting # of Blocks of consecutive cells between given value

[pd15101]

Hi all,

Having a little drama with a roster redesign I'm doing.

Each employee has a row, 365 cells long, representing the entire year. Days off are marked "X", while days worked are filled with a numerical start time.

To ensure the employees are rostered as equally as possible, I would like a way to count the number of blocks of >7 consecutive shifts. What this means is the number of times there is at least 7 cells between two cells which contain "X".

The count is then used in a separate sheet to compare against other employees.

I've nutted out how to count the number of weekend shifts for my other analysis, but this one has me stumped.

Any help appreciated!

 

[Fennek]

Hi,

as "X" is text and the time is a number, the function specialcells(2,1) = numbers and specialcells(2,2) = text the entries are distinguish.

Without testing then count of more than 7 shifts are:

iRow = 2
for each Ar in rows(iRow).specialcells(2,1).areas
      if Ar.count > 7 then iShifts = iShifts + 1
next Ar
msgbox "Number of shifts > 7: " & iShifts

regards

 

[pd15101]

Hi Fennek,

Thanks for the quick reply. I have only used the formula line before.

How do I use the code? Or, preferably, is there a way to do it using a formula?

Thanks again!