Macro to count consecutive blank cells and assign a number

**voitoosh** · 07-03-2013, 10:53 AM

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 ;(

**tigeravatar** · 07-03-2013, 10:59 AM

voitoosh,

Welcome to the forum!
Unfortunately, your question is vague and the logic used seems non-existent. Why was the first block of blank cells ignored? Why was the answer 1? Why wasn't the second block of blank cells looked at? If you don't want the max number of consecutive blanks, what answer are you looking for? If you were to do this manually, what are the exact steps, in order, that you would take in order to arrive at the answer?

**voitoosh** · 07-03-2013, 11:11 AM

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?

**tigeravatar** · 07-03-2013, 11:23 AM

alright...
You have 5 workdays followed by 6 blanks, which is potentially 11 work days. Shouldn't that be counted? If not, why not?
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?

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?

**voitoosh** · 07-03-2013, 11:48 AM

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!

**voitoosh** · 07-04-2013, 06:06 AM

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!

**tigeravatar** · 07-05-2013, 07:47 AM

voitoosh,

Attached is an example workbook based on the criteria you described.
Cell B1 contains a drop-down list where you can pick the month
Cell B2 contains a drop-down list where you can pick the year
The sheet containing those lists is on sheet 'Lists' and they are named ranges listMonths and listYears.
Cell B3 uses a formula to calculate the number of days for the selected Month and Year:

=IF(COUNTBLANK(B1:B2)>0,"",DAY(EOMONTH(DATE(B2,MATCH(B1,listMonths,0),1),0)))

Then in row 5 are headers.
Column A contains names and Column B contains their employee ID's (these are made up in my example)
In cell C5 and copied right is this formula to show the day number (and it will only go to the number of days for the selected month):

=IF(OR(COLUMNS($C5:C5)>$B$3,$B$3=""),"","Day "&COLUMNS($C5:C5))

In cell AI6 and copied down is this formula to get the number of consecutive blanks greater than 5:

=IF($B$3="","",CountConsecutiveBlanks(C6:INDEX(C6:AG6,0,$B$3),5,TRUE))

In cell AI7 and copied down is this formula to get the number of cells for each block of consecutive blanks greater than 5:

=IF($B$3="","",CountConsecutiveBlanks(C6:INDEX(C6:AG6,0,$B$3),5,FALSE))

Those formulas utilize a UDF (User Defined Function) that I created named CountConsecutiveBlanks which is defined with this code:

Public Function CountConsecutiveBlanks(ByVal rRange As Range, _
                                       ByVal lThreshold As Long, _
                                       Optional ByVal bCountOverThreshold As Boolean = True) As Variant
    'This function can be used in two ways
    'By default it counts the number of times that consecutive blank cells are greater than the Threshold
    'If bCountOverThreshold is set to FALSE, this function will return the individual cell counts for blank cells _
     where the consecutive blank cell streak is greater than the threshold
    
    Dim rCell As Range
    Dim lBlankCounter As Long
    Dim lBlanksOverThreshold As Long
    Dim sBlankCount As String
    
    For Each rCell In rRange.Cells
        If IsEmpty(rCell) Then
            lBlankCounter = lBlankCounter + 1
        Else
            If lBlankCounter > lThreshold Then
                lBlanksOverThreshold = lBlanksOverThreshold + 1
                sBlankCount = sBlankCount & ", " & lBlankCounter
            End If
            lBlankCounter = 0
        End If
    Next rCell
    If lBlankCounter > lThreshold Then
        lBlanksOverThreshold = lBlanksOverThreshold + 1
        sBlankCount = sBlankCount & ", " & lBlankCounter
    End If
    
    Select Case bCountOverThreshold
        Case True:  CountConsecutiveBlanks = lBlanksOverThreshold
        Case Else:  CountConsecutiveBlanks = Mid(sBlankCount, 3)
    End Select
    
End Function

The formula accepts three arguments:
rRange: This is the range of cells that the formula will check for blanks
lThreshold: This is the threshold of consecutive blanks that must be exceeded in order to be counted. Because you need this to count greater than 5, I have set it to 5 in the formulas shown above.
[bCountOverThreshold]: This is an optional boolean (true/false) value. By default it is set to true. When true, it will count the number of blocks of consecutive blanks that are greater than the threshold. When false, it will give a count of the number of cells for each block of consecutive blanks greater than the threshold.

Note that I have used the Freeze Panes feature to keep the Month/Year and Names visible, as well as the Day #'s, so that you can scroll right and down and still be aware of which employee for what day you're looking at. You will also have to enable macros for the UDF to work properly, otherwise it will display a #NAME? error.

Does that work for you?

**voitoosh** · 07-08-2013, 03:24 AM

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

Macro to count consecutive blank cells and assign a number

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to count consecutive blank cells and assign a number

Re: Macro to count consecutive blank cells and assign a number

Re: Macro to count consecutive blank cells and assign a number

Re: Macro to count consecutive blank cells and assign a number

Re: Macro to count consecutive blank cells and assign a number

Re: Macro to count consecutive blank cells and assign a number

Re: Macro to count consecutive blank cells and assign a number

Re: Macro to count consecutive blank cells and assign a number

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions