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" wrote:

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