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
On Sep 26, 4:30 am, Johnnyboy5 wrote:

- 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

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

On 26 Sep., 18:13, Johnnyboy5 wrote:
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
On Sep 26, 4:30 am, Johnnyboy5 wrote:

- 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

On 27 Sep, 07:43, Per Jessen wrote:
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

On 26 Sep., 18:13, Johnnyboy5 wrote:



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
On Sep 26, 4:30 am, Johnnyboy5 wrote:

- 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

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

On Sun, 26 Sep 2010 09:13:06 -0700 (PDT), Johnnyboy5
wrote:

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

On 27 Sep, 11:00, Ron Rosenfeld wrote:
On Sun, 26 Sep 2010 09:13:06 -0700 (PDT), Johnnyboy5

wrote:
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

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

No problem!

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

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

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

On 27 Sep, 19:05, Per Jessen wrote:
No problem!

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

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

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Fantastic - worked first time.

Johnny

On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5
wrote:

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

On 27 Sep, 19:44, Ron Rosenfeld wrote:
On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5

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

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

On 27 Sep, 19:44, Ron Rosenfeld wrote:
On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5

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

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

On 27 Sep, 19:44, Ron Rosenfeld wrote:
On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5

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

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

On 27 Sep, 21:04, Johnnyboy5 wrote:
On 27 Sep, 19:44, Ron Rosenfeld wrote:





On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5

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

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

On Mon, 27 Sep 2010 13:04:21 -0700 (PDT), Johnnyboy5
wrote:

On 27 Sep, 19:44, Ron Rosenfeld wrote:
On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5

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

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