Count Consecutive occurences

[gottimd]

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?

 

[Joe Was]

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

 

[gottimd]

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.

 

[Joe Was]

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.

 

[tmischler]

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

Sub Consecutive()

Dim wholerange, myrange As Range
Set wholerange = Range(ActiveCell.Address & ":" & ActiveCell.End(xlToRight).Address)

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

 

[Joe Was]

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