You don't need to use UCASE or its equivalent in the WorksheetFunction
method as the COUNTIF function is not case sensitive; however, applying the
TRIM function is a little more problematic (although I would point out the
your coded solution is not a direct equivalent of the worksheet function
method you posted originally). Let me think about that one a little bit.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Ron,

Similar repone to Rick. Why have you included Application when it works
without.

Like yourself I've posted this answer many times to have the OP bounce
back
'It doesn't get all the values I expect......" and almost invariably it's
a
case and/or rogue space issue so how can you include UCASE(Trim in the
worksheet.function method?

Mike

"Ron de Bruin" wrote:

To duplicate Mike's macro use

Count = Application.WorksheetFunction.CountIf(Range("A1:D2 00"),
"*Boo*")


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rick Rothstein" wrote in message
...
But if you have a particular reson for wanting a macro then use this

Sub marine()
Dim Myrange As Range
Set Myrange = Range("A1:D200")
For Each c In Myrange
If UCase(Trim(c.Value)) = "BOO" Then
Count = Count + 1
End If
Next
MsgBox Count
End Sub

Or...

Sub marine() ' <g
Dim Count As Long
Count = Application.WorksheetFunction.CountIf(Range("A1:D2 00"), "Boo")
MsgBox Count
End Sub

--
Rick (MVP - Excel)