| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
Hi,
You don't need a macro this will do it =COUNTIF(A1  200,"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 200")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! |
|
|
|
|
|||
|
Guest
Posts: n/a
|
> But if you have a particular reson for wanting a macro then use this
> > Sub marine() > Dim Myrange As Range > Set Myrange = Range("A1 200")> 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 200"), "Boo")MsgBox Count End Sub -- Rick (MVP - Excel) |
|
|
|
|
|||
|
Guest
Posts: n/a
|
To duplicate Mike's macro use
Count = Application.WorksheetFunction.CountIf(Range("A1 200"), "*Boo*")-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rick Rothstein" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... >> But if you have a particular reson for wanting a macro then use this >> >> Sub marine() >> Dim Myrange As Range >> Set Myrange = Range("A1 200")>> 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 200"), "Boo")> MsgBox Count > End Sub > > -- > Rick (MVP - Excel) > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Hi,
I'd prefer Count = WorksheetFunction.CountIf(Range("A1 200"), "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 200")> > 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 200"), "Boo")> MsgBox Count > End Sub > > -- > Rick (MVP - Excel) > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
See my reply Mike
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike H" <(E-Mail Removed)> wrote in message news:746B6D60-C26A-4F79-9BEE-(E-Mail Removed)... > Hi, > > I'd prefer > Count = WorksheetFunction.CountIf(Range("A1 200"), "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 200")>> > 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 200"), "Boo")>> MsgBox Count >> End Sub >> >> -- >> Rick (MVP - Excel) >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > To duplicate Mike's macro use > > Count = Application.WorksheetFunction.CountIf(Range("A1 200"), "*Boo*")> > > -- > > Regards Ron de Bruin > http://www.rondebruin.nl/tips.htm > > > "Rick Rothstein" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >>> But if you have a particular reson for wanting a macro then use this >>> >>> Sub marine() >>> Dim Myrange As Range >>> Set Myrange = Range("A1 200")>>> 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 200"), "Boo")>> MsgBox Count >> End Sub >> >> -- >> Rick (MVP - Excel) >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 200"), "*Boo*")> > > -- > > Regards Ron de Bruin > http://www.rondebruin.nl/tips.htm > > > "Rick Rothstein" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > >> But if you have a particular reson for wanting a macro then use this > >> > >> Sub marine() > >> Dim Myrange As Range > >> Set Myrange = Range("A1 200")> >> 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 200"), "Boo")> > MsgBox Count > > End Sub > > > > -- > > Rick (MVP - Excel) > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:%23GlRV$(E-Mail Removed)... >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" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> To duplicate Mike's macro use >> >> Count = Application.WorksheetFunction.CountIf(Range("A1 200"), "*Boo*")>> >> >> -- >> >> Regards Ron de Bruin >> http://www.rondebruin.nl/tips.htm >> >> >> "Rick Rothstein" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >>>> But if you have a particular reson for wanting a macro then use this >>>> >>>> Sub marine() >>>> Dim Myrange As Range >>>> Set Myrange = Range("A1 200")>>>> 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 200"), "Boo")>>> MsgBox Count >>> End Sub >>> >>> -- >>> Rick (MVP - Excel) >>> > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... > > To duplicate Mike's macro use > > > > Count = Application.WorksheetFunction.CountIf(Range("A1 200"), "*Boo*")> > > > > > -- > > > > Regards Ron de Bruin > > http://www.rondebruin.nl/tips.htm > > > > > > "Rick Rothstein" <(E-Mail Removed)> wrote in message > > news:(E-Mail Removed)... > >>> But if you have a particular reson for wanting a macro then use this > >>> > >>> Sub marine() > >>> Dim Myrange As Range > >>> Set Myrange = Range("A1 200")> >>> 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 200"), "Boo")> >> MsgBox Count > >> End Sub > >> > >> -- > >> Rick (MVP - Excel) > >> > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Macro for a "counter" | Lineman116 | Microsoft Excel Misc | 2 | 11th Mar 2009 07:37 PM |
| Read ".dll" files, the Macro "work flow" and the actual values of the variables when Macro is running | leejinhoo@gmail.com | Microsoft Excel Programming | 5 | 16th May 2007 08:18 PM |
| Session("counter") vs. ViewState("counter")...a newbie question | The Eeediot | Microsoft ASP .NET | 3 | 22nd Dec 2004 09:31 PM |
| Nothing happens when I click "Tools" then "Macro" or "Security" in Outlook 2003 | MikeM | Microsoft Outlook Discussion | 1 | 18th Dec 2003 10:31 PM |
| how to assign a "hot-key" or "Macro" to Word "strikethough" feature | =?Utf-8?B?TWlrZQ==?= | Microsoft Word Document Management | 3 | 5th Dec 2003 10:24 PM |
