# Count Consecutive occurences

#### gottimd

##### Well-known Member
If I have in row one the following

1 0 1 1 0 2 1 1 1 0

How do get it to count the number of occurences in row. As in each column represents a week, so in the above example, lets say we are in week 8, there would be two 1's in a row, so the result of the formula would give me something like "Two 1's in a row". There will only be 3 combinations (0,1,2).

Is this possible?

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You could automate this with a sheet event!
As it is you must run it as a macro.

Sub mySeries()
'Standard sheet module code, like: Sheet1.
Dim Message, Title, Default, myLoc

Message = "Enter a Cell address to check:"
Title = "Check Series!"
Default = "A1"

'Display message, title, and default value and get address.
myLoc = InputBox(Message, Title, Default)

Range(myLoc).Select
If Range(myLoc) = "" Then GoTo myEnd

If Range(myLoc).Value = Range(myLoc).Offset(0, 1).Value Then
MsgBox "Two in a row!"
Else
MsgBox "Only one!"
End If

myEnd:
End Sub

Well this would be looking at 30 different rows, so I was hoping that instead of having to input numbers within an input box, that it would automatically look at each row, and in the last column of the row, if there was a consecutive result, put what the consecutive result was like in my example above, in the last column, it would say "Two 1's in a row" in the cell of the last column in that row, because I want to do some vlookups off of these results.

Where do you want the answer of each row's test printed?

Where do you want to start testing: always in column 8 as you said or do you want to thest each cell of each row?
If so your data will need to be rearranged to accomodate the messages!

To test rows by columns it uses a simple loop that you can add to my code to replace the InPutBox.

click on the appropriate cell and run the following macro...

Sub Consecutive()

Dim wholerange, myrange As Range

Dim myvalue, mycount As Integer
myvalue = ActiveCell.Value
mycount = 0

For Each myrange In wholerange
If myrange.Value = myvalue Then
mycount = mycount + 1
Else
MsgBox "There are " & mycount & " " & myvalue & "'s in a row."
End If

Next myrange

End Sub

Just change the working range to your range to check!

Sub mySeriesLoop()
'Standard sheet module code, like: Sheet1.

Range("A1").Select
For Each c In Range("A1:G3")
If (c.Value = "" Or c.Offset(0, 1).Value = "") Then MsgBox "Is Blank!"
c.Select
If ((c.Value = c.Offset(0, 1).Value) And c.Column < 7) Then
MsgBox "Two in a row!"
Else
MsgBox "Only one!"
End If
c.Offset(0, 1).Select

myNext:
Next c

End Sub

Replies
3
Views
148
Replies
2
Views
379
Replies
4
Views
280
Replies
0
Views
454
Replies
1
Views
204

1,203,681
Messages
6,056,708
Members
444,885
Latest member
Mark Prillman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back