"Counter" in macro

[bobkap]

Can anyone help me with this please?
I want to run a macro that loops through many rows of data and counts each
time a certain condition is met. For example, lets say I have 200 rows of
data that's 4 columns wide. I want to know how many times the word "boo" has
occurred in any of these 800 cells.

Thanks!

 

[Mike H]

Hi,

You don't need a macro this will do it

=COUNTIF(A1200,"Boo")

But if you have a particular reson for wanting a macro then use this

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

Mike

 

[Rick Rothstein]

But if you have a particular reson for wanting a macro then use this

Sub marine()
Dim Myrange As Range
Set Myrange = Range("A1200")
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("A1200"), "Boo")
MsgBox Count
End Sub

 

[Ron de Bruin]

To duplicate Mike's macro use

Count = Application.WorksheetFunction.CountIf(Range("A1200"), "*Boo*")

 

[Mike H]

Hi,

I'd prefer
Count = WorksheetFunction.CountIf(Range("A1200"), "Boo")
no need for application

I did it that way because I wanted to include Ucase(trim....
What's the syntax for including that in worksheet.function method?

Mike

 

[Ron de Bruin]

See my reply Mike

 

[Rick Rothstein]

I don't think so. This line from Mike's posting...

If UCase(Trim(c.Value)) = "BOO" Then

tests the entire cell's (upper-cased) content for being equal to the search
word ("BOO")... your asterisks allow the search word to be part of a larger
piece of text and still produce a match to be counted.

 

[Mike H]

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

To duplicate Mike's macro use

Count = Application.WorksheetFunction.CountIf(Range("A1200"), "*Boo*")

 

[Ron de Bruin]

Hi Rick

Correct: If there are more words in the cell a loop is the best way

 

[Mike H]

As in

Bootcamp
Bootleg
Deboochery (Ok so I spelt that wrong)

Mike

 

[Ron de Bruin]

hi mike

I also use a macro if I want to do this but maybe one of the formula guys will jump in with
a good solution



--

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

As in

Bootcamp
Bootleg
Deboochery (Ok so I spelt that wrong)

Mike

 

[Rick Rothstein]

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)

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