| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for a "counter" | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Read ".dll" files, the Macro "work flow" and the actual values of the variables when Macro is running | Excel Programming | |||
| How do I set up a "roll over" counter in excel 2003? | Excel Worksheet Functions | |||
| multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)" | Excel Discussion (Misc queries) | |||
Linear Mode
