Macro to count consecutive blank cells and assign a number

Hi all,

I am new and got a difficult project at work. I amdesperately trying to solve this issue:

I have a line of someone's roster. 31 days. I am to build a model that will see this roster, and if it sees a 'block' of consecutive blank cells, it will count how many are there in this 'block' and assign a number of day off (that will be specified by the user), but let it be 1 for starters.

So it may look like:

[] [] [] WORK WORK WORK WORK [] [] [] [] [] [] OFF OFF OFF WORK [] [] [] [] [] WORK WORK WORK WORK etc.
In the above example, the formula/VBA should look at it in this way: 3 blanks - (no action needed), 4 WORK (no action needed, 6 BLANKS (assign "1" to a specified cell (at the end of the line), etc. So it's like scanning through the row, finding consecutive blanks and with this condition, spit out a number of days off i will need to give someone extra.

I have already gathered and used an array formula: ={MAX(FREQUENCY(IF(C5:AG5="",COLUMN(C5:AG5)),IF(C5:AG5<>"",COLUMN(C5:AG5))))} to count MAX number, but this gives me only the biggest number of consecutive blanks.

Also, I used an array formula to count how many blocks of >5 blank cells are there:
={SUM(0+(FREQUENCY(IF(C4:AG4="",COLUMN(C4:AG4)),IF(C4:AG4<>"",COLUMN(C4:AG4)))>5))}
but again, this only gives me the number of such blocks.

Pretty desperate for this and don't know how to tackle it ;(

Hi TigerAvatar,
Thanks for Your help.

Why was the first block of blank cells ignored?
Because I want it to scan through the roster, it can ignore the blocks of blanks less than 5.

Why was the answer 1?
It is basically a rule for myself. So I have 31 days in a month and have to assume 12 days off (I also assume that someone will be working for the remaining 19 days). Some of the work has already been assigned, and so have been days off. So for example, if the roster looks like this:
A1-------------------------------------------------------------------------->AE1
W W W W W [] [] [] [] [] [] O O O O [] [] [] [] [] [] [] [] W W [] [] [] [] O O
You have 31 days there (W: WORK, [] blank and O for Day off).
So in order to fulfil the criteria (19 work days, 7 have been put down already, so it leaves me 12 to place in the blanks). 12 days off: 6 have been placed already, so i need to assign 6 somewhere, BUT there is another condition: employee can't work >5days without a day off, and that's where I need the macro for, it will scan through these blocks of [] cells.

So based on this example, it should count blanks as: F1:K1 = 6, P1:W1 = 8 blanks, Z1:AC1 = 4 blanks.
That's why only blocks of blanks >5 matter to me really.

Is this explanation any better?

You have 5 workdays followed by 6 blanks, which is potentially 11 work days. Shouldn't that be counted? If not, why not?
After 5 work days, there should be 2 days off, after 4, should be one if I can't fit 2 in. It's a flexible roster so it's all sorts but its the play that kills me...


You also have two work days followed by 4 blanks, which is potentially 6 work days. So shouldn't that have the potential to be counted as 1?
That means I could assign 3 more days off but then i need to assume 2 days off after this. What is next is irrelevant, this is the hardest rule, 5 work days, I need to assume 2 days off.

I guess I don't really understand what your end goal is. What you're counting and why seems to be arbitrary at best. Do the number of days off change for a 30 days month? What about February and leap years?
They do, that's why I was hoping that the macro will count it from the parameter given: e.g. cell A1:AE1 and the range will be included, because otherwise, i suppose it will break.


...
Have a look at the example I quickly prepared and see the orange cells with EXTRA typed in them. This is what I had to enter manually, because people need to have d/o after 5 work days. Also, they need to get their 11 days off in a month, so if i have used all, it's ok, if i need to squeeze a couple of more days off, that's my bloody job to do so

Hope that helped a bit,
I realy appreciate your help!

CHANGE OF PLAN
Can I make this array formula:
={SUM(0+(FREQUENCY(IF(J3:AN3="",COLUMN(J3:AN3)),IF(J3:AN3<>"",COLUMN(J3:AN3)))>5))}
work in a way that when it looks at a row (J3:AN3) and finds columns of consecutive blank cells >5, it will return the number of cells in each of these columns?

Example:
J3:
[][][][][][]WWWWW[][][][][]WWWW[][][]OOO[][][][][][][] etc.
So this formula if applied, will return 2, because in this row, there are 2 columns of consecutive blanks >5 cells,
but can I make it to return the number of these blank cells in this column?
so it'd return: 6 (for the first six blanks) and 7 (for the last column)?

This will greatly improve my planning process,
Thanks a lot guys!

Hi tigeravatar!

Thanks for Your help, it works just fine. To be honest, I was most worried about the macro,

Is there any way I could change some parameters?
My roster lines have slightly different labels than W or O so i would like to change these and couldn't find them in the VBA window.

Also, because my roster has 5+- days on each side, e.g. 27AUG 28AUG 29AUG 30AUG 31 AUG -SEPTEMBER - 01 OCT 02 OCT 03OCT 04OCT 05OCT
I will need to program is so when I import a new month to the sheet, it will see how many days this month has and take it from there (which is: do the blanks>5 calculation.

Much appreciated!
Many thanks for your help.
voitoosh