Count by Colour (Text) with other criteria

[Paul S]

I am using excel 2000

I have used the following Formula to count the number of times that ABC
occurs in red text within the listed range, and it works fine

=PERSONAL.XLS!CountByColorText($G$4:$R$211,3,TRUE, "ABC")

I now want to count the number of times that (F) occurs in red text
within the listed range, the difference being that (F) is always only
the last three digits in the cell eg ABC (F)

I have tried these formulae

=PERSONAL.XLS!CountByColorText($G$4:$R$211,3,TRUE, "*(F)*")

{=PERSONAL.XLS!CountByColorText(RIGHT($G$4:$R$211, 3),3,TRUE, "(F)")}

=SUMPRODUCT(--(NOT(ISERROR(SEARCH("F",$G$4:$R$211))*(PERSONAL.XLS!CountByColorText($G$4:$R$211,3,TRUE)))))


None of which gave the correct answer

My UDF code is as follows

Code:
Function CountByColorText(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False, _
Optional Str As String = "") As Long

Dim Rng As Range
Dim CheckStr As Boolean
Application.Volatile True

For Each Rng In InRange.Cells
CheckStr = False
If Str = "" _
Or LCase(Rng.Value) = LCase(Str) Then
CheckStr = True
End If

If CheckStr = True Then
If OfText = True Then
CountByColorText = CountByColorText - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColorText = CountByColorText - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng

End Function__________________



Any help with a formula that will count (F) when it appears as part of
the contents of a cell and is red text much appreciated

In the Example attached, I want the result in cell d14 to be 1


+-------------------------------------------------------------------+
|Filename: count (F).zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=98|
+-------------------------------------------------------------------+

 

[Dave Peterson]

I think you're going to have to modify your function to support as much as you
want.

For instance, you could use an asterisk for a wildcard "*(F)" to represent ends
with "(F)". You could use "*(F)*" for contains "(F)".

If you're industrious, you could use use the same "escape sequences" that excel
uses:
~* represents an asterisk (not tilde, then wild card)
~? represents a question mark (not tilde, then a single character wild card)
~~ represents a single tilde (no escape sequence)

And if you're really industrious, you may want to learn about regular
expressions.

But if you're only semi-industrious:

Option Explicit
Function CountByColorText(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False, _
Optional Str As String = "") As Long

Dim Rng As Range
Dim CheckStr As Boolean
Dim HasWildCards As Boolean
Application.Volatile True

HasWildCards = CBool(InStr(1, Str, "*", vbTextCompare) > 0)

For Each Rng In InRange.Cells
CheckStr = False
If Str = "" Then
CheckStr = True
End If
If HasWildCards Then
If LCase(Rng.Value) Like LCase(Str) Then
CheckStr = True
End If
Else
If LCase(Rng.Value) = LCase(Str) Then
CheckStr = True
End If
End If

If CheckStr = True Then
If OfText = True Then
CountByColorText = CountByColorText - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColorText = CountByColorText - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng

End Function

======
Google in the *excel* newsgroups for regular expressions and you'll find lots of
hits.

 

[Paul S]

Thx Dave, as I'm a novice in VBA and up against a deadline I took your
semi-industrious option, which worked fine

Paul

I think you're going to have to modify your function to support as much
as you
want.

For instance, you could use an asterisk for a wildcard "*(F)" to
represent ends
with "(F)". You could use "*(F)*" for contains "(F)".

If you're industrious, you could use use the same "escape sequences"
that excel
uses:
~* represents an asterisk (not tilde, then wild card)
~? represents a question mark (not tilde, then a single character wild
card)
~~ represents a single tilde (no escape sequence)

And if you're really industrious, you may want to learn about regular
expressions.

But if you're only semi-industrious:

Option Explicit
Function CountByColorText(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False, _
Optional Str As String = "") As Long

Dim Rng As Range
Dim CheckStr As Boolean
Dim HasWildCards As Boolean
Application.Volatile True

HasWildCards = CBool(InStr(1, Str, "*", vbTextCompare) > 0)

For Each Rng In InRange.Cells
CheckStr = False
If Str = "" Then
CheckStr = True
End If
If HasWildCards Then
If LCase(Rng.Value) Like LCase(Str) Then
CheckStr = True
End If
Else
If LCase(Rng.Value) = LCase(Str) Then
CheckStr = True
End If
End If

If CheckStr = True Then
If OfText = True Then
CountByColorText = CountByColorText - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColorText = CountByColorText - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng

End Function

======
Google in the *excel* newsgroups for regular expressions and you'll
find lots of
hits.

Paul S wrote:-

+-------------------------------------------------------------------+
+-------------------------------------------------------------------+