0

Hi I have an spreadsheet with the following structure:-

Example Spreadsheet

I need help to figure out a formula or VBA function, to calculate the Activity Block Count column.

This is the count of non blank, contiguous cells in the date columns. For example Project 5 has 4 weeks of activity but only 3 continuous blocks of activity.

Any idea warmly welcomed ;-). I'm working at the weekend and it's driving me insane!

Thanks

Jonathan

Jonathan Lyon
  • 3,862
  • 7
  • 39
  • 52
  • Are those typed values or formulas returning the weekly numbers? –  Jul 28 '18 at 02:21
  • Week Count is a formula =count(range of columns with dates) and Activity Block Count is just a typed in value that I'd like to replace with a formula or function. Dates across the top will be in date order but may not be sequential. Does that answer your question? – Jonathan Lyon Jul 28 '18 at 02:24
  • I meant the numbers in the weeks like 120,987 or 23,184. –  Jul 28 '18 at 02:26
  • they're just values, not formula results – Jonathan Lyon Jul 28 '18 at 02:37

2 Answers2

2

Just walk through the cells in the range, noting changes in whether the cell(s) are populated or not.

Function countActivityBlock(rng As Range) As Long

    Dim r As Range, bNum As Boolean

    If Not IsEmpty(rng.Cells(1)) Then
        bNum = True
        countActivityBlock = 1
    End If

    For Each r In rng
        If Not IsEmpty(r) And Not bNum Then
            bNum = Not bNum
            countActivityBlock = countActivityBlock + 1
        ElseIf IsEmpty(r) And bNum Then
            bNum = Not bNum
        End If
    Next r

End Function

enter image description here

SpecialCells(xlCelltypeConstants, xlNumbers) does not work within a UDF.

  • just returns 1 if I use the formula =countActivityBlock(E8:Q8) in any row. Any ideas? – Jonathan Lyon Jul 28 '18 at 02:35
  • Looks like this isn't going to work. The blank cells are being read as xlNumbers, something I didn't anticipate. I'll run a few more tests. –  Jul 28 '18 at 02:49
  • This works in a sub or the Immediate window but apparently not within a UDF. –  Jul 28 '18 at 03:03
  • ok cool - thanks! I was trying to loop through the cells in the range but can't define the logic for a running count that doesn't count cells if they have a value but are adjacent to another cell with a value – Jonathan Lyon Jul 28 '18 at 03:03
  • See revision above. –  Jul 28 '18 at 03:25
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/176918/discussion-between-jonathan-lyon-and-jeeped). – Jonathan Lyon Jul 28 '18 at 03:33
0

The formula way of doing this is using FREQUENCY as follows

=SUM(--(FREQUENCY(IF(D2:P2<>"",COLUMN(D2:P2)),IF(D2:P2="",COLUMN(D2:P2)))>0))

Must be entered as an array formula using CtrlShiftEnter

See This and This

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37