counting a specific "text" occurance in a selection of sheets withina workbook

J

Johnnyboy5

Hi

can anyone help me modify the macro below that Don sent to me.

I need it to select a specific range of worksheets. example sheets
1 to 12 named (April, May, June, July ....etc)

rather than selecting all the sheets in the workbook.

thanks

Johnny




Don Guillett Excel MVP
View profile
More options 26 Sep, 13:26

- Show quoted text -
This might be quicker than a loop for text.
Option Explicit
Sub countjune()
Dim ws As Worksheet
Dim mycol As Range
Dim mc As Long
For Each ws In Worksheets
Set mycol = ws.Columns("N")
mc = mc + Application.CountIf(mycol, "Hello")
Next ws
MsgBox mc
End Sub
 
P

Per Jessen

If the sheets are placed as the first 12 tabs, it can be done like
this:

Sub countjune()
Dim ws As Worksheet
Dim mycol As Range
Dim mc As Long
Dim shIndex As Long

For shIndex = 1 To 12
Set ws = Worksheets(shIndex)
Set mycol = ws.Columns("N")
mc = mc + Application.CountIf(mycol, "Hello")
Next
MsgBox mc
End Sub

Regards,
Per
 
J

Johnnyboy5

If the sheets are placed as the first 12 tabs, it can be done like
this:

Sub countjune()
Dim ws As Worksheet
Dim mycol As Range
Dim mc As Long
Dim shIndex As Long

For shIndex = 1 To 12
Set ws = Worksheets(shIndex)
Set mycol = ws.Columns("N")
mc = mc + Application.CountIf(mycol, "Hello")
Next
MsgBox mc
End Sub

Regards,
Per

Thank you both (Per & Ron) for two great solutions both work well.

I am going to use the date one – just in case others get their hands
on it and mess about with it in work (technophobes) and keep the other
for another workbook I have to set up.

I would really love it – if after the message comes up if the answer
could also populate a cell in another sheet.

Say cell P18 in worksheet “Main Totals” in the same workbook as the
other data.

Many thanks for your input to my learning

Johnnyboy
 
J

Johnnyboy5

Change to:

For Each ws In Worksheets
   Select Case ws.name
        Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sep", "Oct", "Nov", "Dec"
   Set mycol = ws.Columns("N")
   mc = mc + Application.CountIf(mycol, "Hello")
end select
Next ws

Thank you both (Per & Ron) for two great solutions both work well.

I am going to use the date one – just in case others get their hands
on it and mess about with it in work (technophobes) and keep the other
for another workbook I have to set up.

I would really love it – if after the message comes up if the answer
could also populate a cell in another sheet.

Say cell P18 in worksheet “Main Totals” in the same workbook as the
other data.

Many thanks for your input to my learning

Johnnyboy
 
P

Per Jessen

No problem!

After the 'MsgBox mc' line, insert the line below:

Worksheets("Main Totals").Range("P18")=mc

Regards,
Per
 
J

Johnnyboy5

No problem!

After the 'MsgBox mc' line, insert the line below:

Worksheets("Main Totals").Range("P18")=mc

Regards,
Per

Fantastic - worked first time.

Johnny
 
J

Johnnyboy5

Although you could certainly add a line like:

worksheets("Main Totals").range("P18").value = mc

I would rewrite it as a function, and enter that UDF into the cell
where you want the result.

In doing that, you could set it up so that you could also allow for
some variables.  As written, I've allowed for the function to specify
the string to search for, and also the worksheets to examine.  If you
do not specify any worksheets, the function will look at all
worksheets that have the three letter month abbreviation as the name;
but if you specify any sheets, it will look only at those sheets.

Just an example of some of the things that can be done.

===================================
Option Explicit
Function CountStuff(SearchString As String, _
        ParamArray MonthNames() As Variant) As Long
  Dim sMonths() As String
  Dim i As Long
  Dim ws As Worksheet, ValidWS As Boolean
  Dim mycol As Range
  Dim mc As Long

  'If no months are specified then check all months
    If UBound(MonthNames) = -1 Then
        ReDim sMonths(1 To 12)
        For i = 1 To 12
            sMonths(i) = Format(DateSerial(2000, i, 1), "mmm")
        Next i
      Else
        ReDim sMonths(1 To UBound(MonthNames) - _
        LBound(MonthNames) + 1)
        For i = LBound(MonthNames) To UBound(MonthNames)
            sMonths(i + IIf(LBound(MonthNames) = 0, _
                1, 0)) = MonthNames(i)
        Next i
    End If

For Each ws In Worksheets
    ValidWS = False
    On Error Resume Next
        ValidWS = WorksheetFunction.Match(ws.Name, sMonths, 0)
    On Error GoTo 0
    If ValidWS Then
        Set mycol = ws.Columns("N")
        mc = mc + WorksheetFunction.CountIf(mycol, SearchString)
    End If
Next ws

MsgBox (mc) 'you might not want this line
CountStuff = mc

End Function
===================================

Wow - thanks for all that - I copied and pasted into VBA - but I
couldn't get it to run. Should it be added to the other macro or is
it a stand alone.

I am quite new to all this jazz

thanks

John
 
J

Johnnyboy5

Although you could certainly add a line like:

worksheets("Main Totals").range("P18").value = mc

I would rewrite it as a function, and enter that UDF into the cell
where you want the result.

In doing that, you could set it up so that you could also allow for
some variables.  As written, I've allowed for the function to specify
the string to search for, and also the worksheets to examine.  If you
do not specify any worksheets, the function will look at all
worksheets that have the three letter month abbreviation as the name;
but if you specify any sheets, it will look only at those sheets.

Just an example of some of the things that can be done.

===================================
Option Explicit
Function CountStuff(SearchString As String, _
        ParamArray MonthNames() As Variant) As Long
  Dim sMonths() As String
  Dim i As Long
  Dim ws As Worksheet, ValidWS As Boolean
  Dim mycol As Range
  Dim mc As Long

  'If no months are specified then check all months
    If UBound(MonthNames) = -1 Then
        ReDim sMonths(1 To 12)
        For i = 1 To 12
            sMonths(i) = Format(DateSerial(2000, i, 1), "mmm")
        Next i
      Else
        ReDim sMonths(1 To UBound(MonthNames) - _
        LBound(MonthNames) + 1)
        For i = LBound(MonthNames) To UBound(MonthNames)
            sMonths(i + IIf(LBound(MonthNames) = 0, _
                1, 0)) = MonthNames(i)
        Next i
    End If

For Each ws In Worksheets
    ValidWS = False
    On Error Resume Next
        ValidWS = WorksheetFunction.Match(ws.Name, sMonths, 0)
    On Error GoTo 0
    If ValidWS Then
        Set mycol = ws.Columns("N")
        mc = mc + WorksheetFunction.CountIf(mycol, SearchString)
    End If
Next ws

MsgBox (mc) 'you might not want this line
CountStuff = mc

End Function
===================================

Ahh, read stuff.... got it to work, but I did change the months
names in your last post some are four letter not three...any ideas

I see where you are going with this User Designed Function.... nice


Johnny
 
J

Johnnyboy5

Although you could certainly add a line like:

worksheets("Main Totals").range("P18").value = mc

I would rewrite it as a function, and enter that UDF into the cell
where you want the result.

In doing that, you could set it up so that you could also allow for
some variables.  As written, I've allowed for the function to specify
the string to search for, and also the worksheets to examine.  If you
do not specify any worksheets, the function will look at all
worksheets that have the three letter month abbreviation as the name;
but if you specify any sheets, it will look only at those sheets.

Just an example of some of the things that can be done.

===================================
Option Explicit
Function CountStuff(SearchString As String, _
        ParamArray MonthNames() As Variant) As Long
  Dim sMonths() As String
  Dim i As Long
  Dim ws As Worksheet, ValidWS As Boolean
  Dim mycol As Range
  Dim mc As Long

  'If no months are specified then check all months
    If UBound(MonthNames) = -1 Then
        ReDim sMonths(1 To 12)
        For i = 1 To 12
            sMonths(i) = Format(DateSerial(2000, i, 1), "mmm")
        Next i
      Else
        ReDim sMonths(1 To UBound(MonthNames) - _
        LBound(MonthNames) + 1)
        For i = LBound(MonthNames) To UBound(MonthNames)
            sMonths(i + IIf(LBound(MonthNames) = 0, _
                1, 0)) = MonthNames(i)
        Next i
    End If

For Each ws In Worksheets
    ValidWS = False
    On Error Resume Next
        ValidWS = WorksheetFunction.Match(ws.Name, sMonths, 0)
    On Error GoTo 0
    If ValidWS Then
        Set mycol = ws.Columns("N")
        mc = mc + WorksheetFunction.CountIf(mycol, SearchString)
    End If
Next ws

MsgBox (mc) 'you might not want this line
CountStuff = mc

End Function
===================================

not sure whats happening - got it to work (a bit) but its not
counting / looking in all the months - April May June July Aug Sept
Oct Nov Dec Jan Feb Mar some are 3 some are 4

Mmmmm
 
J

Johnnyboy5

not sure whats happening - got it to work (a bit)  but its not
counting / looking in all the months - April May June July Aug Sept
Oct Nov Dec Jan Feb Mar   some are 3  some are 4

Mmmmm

MMMMMM funny - got it - I have changed the months to 3 chr. Now
works ...

What I lot of learning... how can I fix the function into a cell to
run it when I click on it - or is that not the way forward.

John
 
J

Johnnyboy5

MMMMMM funny - got it -  I have changed the months to 3 chr.  Now
works ...

What I lot of learning... how can I fix the function into a cell to
run it when I click on it  -  or is that not the way forward.

John


Yes YES YES got it ...............nice ....loving your work -
got it into the cells etc etc.

THANKS BIG TIME. any more ideas ?


Johnny
 
J

Johnnyboy5

A User defined function will run like any other worksheet function.

So, if in some cell, you have something like:

=CountStuff(A1)

where A1 contains your search string, then whenever you change A1, the
formula will recalculate.

In order to have it change also when you enter an additional search
string in some cell on another sheet, you can add

Application.Volatile

It will then recalculate when anything calculates in the workbook.

The above line is added after the variable declarations.  e.g:

===================
Option Explicit
Function CountStuff(SearchString As String, _
    ParamArray MonthNames() As Variant) As Long
  Dim sMonths() As String
  Dim i As Long
  Dim ws As Worksheet, ValidWS As Boolean
  Dim mycol As Range
  Dim mc As Long

  Application.Volatile
.
.
.
===============================

Ron,

Thank you so much for your help,

I used it today and it worked really well.

Thanks for this latest change - I have changed the macro - and give it
a good work out tomorrow.

Your have been very kind

Johnnyboy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top