Count consecutive cells then start over

[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~

 

[Don Guillett]

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

 

[Herbert Seidenberg]

Or without VBA
and without counting 8x's 9x's ...
http://www.freefilehosting.net/download/3aee3

 

[Don Guillett]

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

 

[John67]

Thanks Don, but unfortunately it didn't do anything. I do appreciate the
help all the same.

 

[T. Valko]

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)

 

[Don Guillett]

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

 

[Don Guillett]

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

 

[Herbert Seidenberg]

I withdraw my previous post.
Biff's approach is the way to go.

 

[ghostman02]

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