Same digit in a row

[hemsleysut]

I have 0s and 1s randomly placed to fill a grid of 20cols. by 120rows. Is there a VBA procedure that will return the total number of rows where a 1 appears 5 times consecutively. Any help is greatly appreciated.

 

[jindon]

Hi

Sub test()
Dim i As Long, ii As Long, n As Long
For i = 1 To 116
     For ii = 1 To 20
          If WorksheetFunction.CountIf(Cells(i,ii).Resize(5),1)=5 Then n = n + 1
     Next
Next
MsgBox n
End Sub

 

[pgc01]

Hi hemsleysut

3 questions:

- if there is more than 1 sequence of 5 1s in a row does it still count just as 1?

- In case the answer was no, what about sequences that overlap? If A1:F1=1 do we have 2 sequences (A1:E1,B1:F1)?

- Why vba?

Kind regards
PGC