Count consecutive cells then start over

  • Thread starter Thread starter John67
  • Start date Start date
J

John67

Hello, I would like to know how to create a simple function which will count
the cells that show up with the number 1 seven consecutive times and then
start over.

For instance, let's say the range is B3:B100, and the number 1 will
occasionally show up 3x's or 5x's and then occasionally 7x's with blank cells
and between each of the sets. I need it to display somewhere at the top of
the spreadsheet when the number 1 has shown up seven consecutive times. Most
importantly, I need it to start the count over again once it runs across a
blank cell.

Any help would greatly be appreciated!

~John~
 
Sub sevenones()
mc = "b"
c = 0
For i = 3 To Cells(Rows.Count, mc).End(xlUp).Row
If Cells(i + 1, mc) = 1 And Cells(i, mc) = 1 Then
c = c + 1
Else
c = 0
End If
'MsgBox c
If c = 7 Then
cells(1,mc)= Cells(i, mc).Address
Exit For
End If
Next i
End Sub
 
If a function is desired, use this formula in any cell desired to locate the
7th consecutive.
=so("e")

Put the below function into a REGULAR vba module
Function so(mc)
c = 0
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
If Cells(i + 1, mc) = 1 And Cells(i, mc) = 1 Then
c = c + 1
Else
c = 0
End If
'MsgBox c
If c = 7 Then
so = Cells(i, mc).Address
'Cells(1, mc) = Cells(i, mc).Address
Exit For
End If
Next i
End Function
 
Thanks Don, but unfortunately it didn't do anything. I do appreciate the
help all the same.
 
Try this array formula** :

=SUM(--(FREQUENCY(IF(List1=1,ROW(List1)),IF(List1<>1,ROW(List1)))=Target))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
It WAS tested.

I didn't but you may need this as the first line in the function.
Application.Volatile

Did you place in a regular module?
Did you change "e" to your column that has the string of 1's?
Is calculation set to automatic? Touch f9 key to calculate
 
Also, try this modification.

Function so(mc)
Application.Volatile
c = 1
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
If Cells(i + 1, mc) = 1 And Cells(i, mc) = 1 Then
c = c + 1
Else
c = 1
End If
'MsgBox c
If c = 7 Then
so = Cells(i + 1, mc).Address
'Cells(1, mc) = Cells(i, mc).Address
Exit For
End If
Next i
End Function
 
flipping subjects a bit I am looking to count the number of "y" in a row but then start over at a blank cell. can the formula skip every other cell? any formulas available?
i.e.
Attendance
E2="Aug 21" G2="Aug 28" I2="Sept 4"
E5="Class" F5="homework" G5="Class" H5="homework" I5="Class" J5="homework"
C6="John" E6="y" E5="y" G6="y" H5="" I6="" J6=""

the first formula should show that John has been to class 2xs in a row
the second formula should show homework turned 1 out of 3 times.

Is this possible? thanks again for any attempt.
Daniel
 
Back
Top