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

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