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

[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

 

[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

 

[Ron Rosenfeld]

For Each ws In Worksheets
Set mycol = ws.Columns("N")
mc = mc + Application.CountIf(mycol, "Hello")
Next ws

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

 

[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

 

[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

 

[Per Jessen]

No problem!

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

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

Regards,
Per

 

[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

 

[Ron Rosenfeld]

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.

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
===================================

 

[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

 

[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

 

[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

 

[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

 

[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

 

[Ron Rosenfeld]

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

What is happening is that the line that sets up the array of Month
Names is setting it up as three letter months which is the standard
method of abbreviating months.

For i = 1 To 12
sMonths(i) = Format(DateSerial(2000, i, 1), "mmm")
Next i

However, your names, as listed above, are inconsistent in that some
months you abbreviate to 3 letters, and others you do not. Some are
three, four and even five (April). But March, which is also a 5
letter month, is abbreviated.

Since there is no simple algorithm to generate the month names, it'll
have to be done using a brute force method. Or, what might be a
preferable solution, you could standardize your sheet naming method.

To set up the array in accordance with your data above, replace the
above For..Next loop with:

=====================
For i = 1 To 12
sMonths(i) = Format(DateSerial(2000, i, 1), "mmmm")
Next i
For i = 1 To 12
Select Case i
Case 1 To 3, 8, 10 To 12
sMonths(i) = Left(sMonths(i), 3)
Case Is = 9
sMonths(i) = Left(sMonths(i), 4)
End Select
Next i
=====================

 

[Ron Rosenfeld]

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

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
..
..
..
===============================

 

[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

 

[Ron Rosenfeld]

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

Glad to help. Thanks for the feedback.