"Counter" in macro
 

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!

"Counter" in macro
 

Hi,

You don't need a macro this will do it

=COUNTIF(A1:D200,"Boo")

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

Mike

"bobkap" wrote:

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!

"Counter" in macro
 

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)

"Counter" in macro
 

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)

"Counter" in macro
 

Hi,

I'd prefer
Count = WorksheetFunction.CountIf(Range("A1:D200"), "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

"Rick Rothstein" wrote:

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)

"Counter" in macro
 

See my reply Mike

--

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


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

I'd prefer
Count = WorksheetFunction.CountIf(Range("A1:D200"), "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

"Rick Rothstein" wrote:

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)

"Counter" in macro
 

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.

--
Rick (MVP - Excel)


"Ron de Bruin" wrote in message
...
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)

"Counter" in macro
 

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)

"Counter" in macro
 

Hi Rick

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

--

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


"Rick Rothstein" wrote in message ...
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.

--
Rick (MVP - Excel)


"Ron de Bruin" wrote in message
...
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)

"Counter" in macro
 

As in

Bootcamp
Bootleg
Deboochery (Ok so I spelt that wrong)

Mike

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Ron de Bruin" wrote in message
...
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)

"Counter" in macro
 

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


"Mike H" wrote in message ...
As in

Bootcamp
Bootleg
Deboochery (Ok so I spelt that wrong)

Mike

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Ron de Bruin" wrote in message
...
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)

"Counter" in macro
 

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)