Almost there, works for anything greater than 2 but not equal to 2. Can i
just add the '=' sign beside ?

Thanks Joel!

"Joel" wrote:

It wan't clear in the original posting that the data was in a range of cells.


=CountAlpha(B1:B20,"S")

Function CountAlpha(Target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For Each cell In Target
ThisChar = Trim(cell)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next cell
If MaxLen 2 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi Joel, it doesn't seem to be working as the result returned with 0 (i was
expecting 3 counts from the sample provided). See below sample column:

S
A
S
S
S
G
S
A
A
S
S
S
S
A
G
A
S
S
A
S

The above was placed in column B to test out the UDF. As the condition was
to track the aphabet "S" if it appears =2 (equal or greater than two) times,
the above sample had 3 instances (B3:B5, B10:B13, and B17:B18). Hence, it
should return a 3 in the formula cell that refers to the UDF. Is this
possible?

P.S. - I made a mistake in the original post of counting the result as 2
instances only when it should be 3 instead. Sorry.


"Joel" wrote:

You need a UDF. Call with the following

=Countalpha(B1,"S")

where B21 is the column Header and "S" is the character yo uare looking for.
You had spaces in the Column Header. I assumed your real column Headers did
not have the spaces.


Function CountAlpha(target As Range, Alpha As String)

LastChar = ""
MaxLen = 0
CharCount = 0
For i = 1 To Len(target)
ThisChar = Mid(target, i, 1)
If (ThisChar = Alpha) And _
(ThisChar = LastChar) Then

CharCount = CharCount + 1
Else
If CharCount MaxLen Then
MaxLen = CharCount
End If
CharCount = 1
End If
LastChar = ThisChar
Next i
If MaxLen 1 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?