Database Functions - Strange results

B

Bob

Cell A1 = "Red"
Cell A2 = "Green, Red"

When I use DCOUNTA to determine the number of "Red" occurrences, the result
is 1 (rather than 2). Can someone explain why this happens (and a solution
for obtaining the correct count)? Thanks for the help.
 
O

Otto Moehrbach

Bob
That happens because the DCOUNTA function counts the number of cells
that contain "Red" as the whole entry in the cell. That's 1.
I don't know if there is a formula way to do what you want, but you can
do it with the following macro. I wrote the macro assuming that your data
is in Column A starting with A1. Also, I assumed you wanted to count all
instances of "Red" regardless of case. Note that all instances of "red"
will be counted even if the "red" is not a separate word. For instance,
"redress" is one instance of "red". Post back if this does not do what you
want. HTH Otto
Sub CountRed()
Dim RngColA As Range
Dim i As Range
Dim c As Long
c = 0
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
If InStr(UCase(i.Value), "RED") > 0 Then c = c + 1
Next i
MsgBox c
End Sub
 
B

Bob

Gary,
That did the trick! Thanks!!!
Bob

Gary L Brown said:
Use a wildcard in your criteria.
Try *Red
HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
Click to expand...
 
Top