Count Number Of Times Values Appears Consecutively, Return Longest

[CVinje]

I have a spreadsheet with data spanning rows in columns F through T.
Different values may be input into the cells; however, I want the number of
times specific values are entered consecutively to be counted and the longest
string be returned. For example:

|F |G |H | I | J |K |L |M |N |O | P | Q |R |S |T |
|W|W|W|BLANK|BLANK|W|W|W|W|W|BLANK|BLANK|W|W|W|

The formula (possibly user defined formula??) would look at the range F:T,
count the number of times "W" appears, and return the count for the longest
consecutive string (in the example above: 5). Another condition that is
needed would be a list of values that should be considered in the
calculation. For example, consider the series as consecutive values if it
reads: W,W,W,18,UNB, then the count would still return 5. These possible
values could be defined in the UDF or somewhere on the sheet (then hidden).

Thanks for your help,

CVinje

 

[BSc Chem Eng Rick]

Hi CVinje

Below is a function for your first request. It accepts the range to be
analysed and returns the longest conseccutive set of W's. It is very simple,
however to get it to recognise various series is considerably more involved,
you can however use this code to recognise multiple strings but NOT in a
particular order. To do this just insert a number of "or" conditions when
checking "MyCell.Value". To use it, insert it in a module and then you can
use the function like anyother in Excel. It even appears in the autocomplete
list.

Function COUNTCONSEC(CellRange As Range) As Long
Dim MyCell As Range
Dim Longest As Long, Count As Long
Count = 0
Longest = 0
For Each MyCell In CellRange
If MyCell.Value = "W" Then
Count = Count + 1
If MyCell.Address = CellRange.Cells(CellRange.Count).Address Then
If Count > Longest Then Longest = Count
End If
ElseIf MyCell.Value <> "W" Or MyCell.Address =
CellRange.Cells(CellRange.Count).Address Then
If Count > Longest Then Longest = Count
Count = 0
End If
Next MyCell
CONSECCOUNT = Longest
End Function

 

[CVinje]

Thank you for the help; however, when I place the UDF in a module and then
try to call it from a cell with a range (i.e. - COUNTCONSEC(F8:T8)) it
returns a zero value. Not sure what I'm missing.

Thanks again,

CVinje

 

[BSc Chem Eng Rick]

My apologies, change the second last line of the code I sent from
"CONSECCOUNT = Longest"
to
"COUNTCONSEC = Longest"

If this helps please click "Yes"
<><><><><><><><><><>

 

[BSc Chem Eng Rick]

Hi there,

Below is the function that will count so long as you follow the series
specified as a text string. e.g. =COUNTCONSEC(A1:Z1,"WWBC")

With a bit of thought you can combine with the first function and have a
complete working solution.

Function COUNTCONSEC2(CellRange As Range, CritStr As String) As Long
Dim MyCell As Range
Dim Longest As Long, Count As Long
Dim MyString As String
Count = 0
Longest = 0
MyString = ""
For Each MyCell In CellRange
MyString = MyString & MyCell.Value
If InStr(CritStr, MyString) = 0 Or InStr(CritStr, MyString) <> 1 Or
IsEmpty(MyCell.Value) Then
If Count > Longest Then Longest = Count
Count = 0
MyString = ""
ElseIf InStr(CritStr, MyString) <> 0 Then
Count = Count + 1
If MyCell.Address = CellRange.Cells(CellRange.Count).Address Then
If Count > Longest Then Longest = Count
End If
If MyString = CritStr Then MyString = ""
End If
Next MyCell
COUNTCONSEC2 = Longest
End Function

 

[CVinje]

Thanks you for the help! I've been able to use the first function with the
fix you provided in your second post. Adding the additional values using the
OR method was confusing at first, especially getting it to check for a value
in the range that was a number. After much searching on the internet, I
learned to use the IsNumeric(MyCell.Value) And Len(MyCell.Value) > 0 method
to get it to check for a number (it was counting blank cells as having a
value greater than 0 because of formatting, etc). Thanks again for the help!!

CVinje