countif won't count

[Dudely]

I'm trying to use the following function:



Sub Find_Bold_Cat()
Dim lCount As Long
Dim rFoundCell As Range


Set rFoundCell = Range("A1")

For lCount = 1 To WorksheetFunction.CountIf(Columns(1), "Cat")

Set rFoundCell = Columns(1).Find(What:="Cat",
After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

With rFoundCell
.ClearComments
.AddComment Text:="Cat lives here"
End With

Next lCount

End Sub

which I found here: http://www.ozgrid.com/VBA/find-method.htm


I made some minor changes to suit my situation, whereby I changed
"cat" to needle and then set needle to "mydomain.com" I also changed
the portion that adds comments to instead delete the row.

The problem is that the "countif" statement is returning zero so it
NEVER ENTERS THE FOR LOOP - it can't find the needle in columns(1)
even though it is without question there. Tracing it shows that
needle is indeed set correctly, and a watch on columns(1) shows
that .value(1,1) and other rows do indeed contain the needle I'm
seeking.

The actual data (haystack) I'm searching through looks similar to
this:
mailto:[email protected]?subject=$240000%20Beautiful
%20%203%20bedroom%20Home%20For%20Sale%20(hend,%20newport%20&%3b
%20racetrack%20rd)

So, I can only surmise that it's having difficulty finding a partial
match. Is this simply the way countif works or is there some trick
I'm missing?

What's the best way to fix the code so it works correctly?

Thank you

 

[Dave Peterson]

=countif("A:A","cat")
will count the number of cells that contain "cat"--nothing else. But since
you're looking at xlpart in the .find statement, maybe you want to find any cell
that contains "Cat" anywhere in the string.

You can use a wildcard to do this:

For lCount = 1 To WorksheetFunction.CountIf(Columns(1), "*Cat*")

In a worksheet formula:
=countif("A:A","*cat*")

 

[Dudely]

Yes I didn't know that countif expects an exact match so that a
regular expression is needed if I'm looking for an inexact match.

Thank you, problem solved.