I added a 3rd parameter to the function for the PassCriteria rather than
writing a new function. Modify your call to the function by adding new
parameter.

You can call a function multiple times from the spreadsheet like you did
with no problems. the functtions do not run at the same time by are called
in order usually from left to right unless you have parenthesis to change the
precedence of the calling order.

Function CountAlpha(Target As Range, Alpha As String, PassCriteria as Integer)

UAlpha = UCase(Alpha)
AlphaLen = Len(Alpha)

MaxLen = 0
StrCount = 0

'Put input data into a string
For Each cell In Target
InputStr = InputStr & cell.Value
Next cell
InputStr = UCase(InputStr)

Position = 1
Do While Position <= Len(InputStr) - AlphaLen + 1

If Mid(InputStr, Position, AlphaLen) = UAlpha Then

StrCount = StrCount + 1
Position = Position + AlphaLen
Else
If StrCount MaxLen Then
MaxLen = StrCount
End If
StrCount = 0
Position = Position + 1
End If
Loop

If StrCount MaxLen Then
MaxLen = StrCount
End If

If MaxLen = PassCriteria Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Thanks Joel!

By the way, can i have more than one UDF CountAlpha function running? The
current one looks for =2. What if i need to have separate formula cell to
look for =3 and =4 (either for 1 text character or 2 text character per
below)?

"Joel" wrote:

Yes, I just made changes so the search string (2nd parameter) can be multiple
characters like "SOS".

"andrew" wrote:

Do i still use this formula in the target cell?

=if(CountAlpha(B1:B20,"S")
CountAlpha(B1:B20,"G"),CountAlpha(B1:B20,"S"),Coun tAlpha(B1:B20,"G"))


"Joel" wrote:

I didn't initially understand what you wanted but thinking about it for a
while I figured out what you were really asking and had the solution in my
head waiting for your latest request.

Function CountAlpha(Target As Range, Alpha As String)

UAlpha = UCase(Alpha)
AlphaLen = Len(Alpha)

MaxLen = 0
StrCount = 0

'Put input data into a string
For Each cell In Target
InputStr = InputStr & cell.Value
Next cell
InputStr = UCase(InputStr)

Position = 1
Do While Position <= Len(InputStr) - AlphaLen + 1

If Mid(InputStr, Position, AlphaLen) = UAlpha Then

StrCount = StrCount + 1
Position = Position + AlphaLen
Else
If StrCount MaxLen Then
MaxLen = StrCount
End If
StrCount = 0
Position = Position + 1
End If
Loop

If StrCount MaxLen Then
MaxLen = StrCount
End If

If MaxLen = 2 Then
CountAlpha = MaxLen
Else
CountAlpha = 0
End If
End Function


"andrew" wrote:

Hi Joel, the formula should return one value as what you've said. However,
what i actually wanted was for the script (UDF) to check for 2 alphabets
which are in sequence (e.g. GSS, SGGG or SSGSS as examples in the table
provided) instead of just one alphabet previously. So, whenever the UDF
detects a sequence it will count as one instance - and keeps adding whenever
another occurs.

"Joel" wrote:

Yes you can change 2 ro = 2. Functions can only return one value so I'm
not sure how youwould track to different values.

=if(CountAlpha(B1:B20,"S")
CountAlpha(B1:B20,"G"),CountAlpha(B1:B20,"S"),Coun tAlpha(B1:B20,"G"))


"andrew" wrote:

By the way, is it possible to track 2 alphabets in a sequence? (i.e. perhaps
"S" and "G")

"andrew" wrote:

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?