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 <intermediatec...@gmail.com> 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 <intermediatec...@gmail.com> 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 <intermediatec...@gmail.com> 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 <perjesse...@hotmail.com> 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 <intermediatec...@gmail.com> 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 <intermediatec...@gmail.com> 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 27 Sep, 11:00, Ron Rosenfeld <r...@nospam.net> wrote:
> On Sun, 26 Sep 2010 09:13:06 -0700 (PDT), Johnnyboy5
>
> <intermediatec...@gmail.com> 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 <perjesse...@hotmail.com> 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 workbookas 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 27 Sep, 19:44, Ron Rosenfeld <r...@nospam.net> wrote:
> On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5
>
> <intermediatec...@gmail.com> 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 <r...@nospam.net> wrote:
> On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5
>
> <intermediatec...@gmail.com> 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 <r...@nospam.net> wrote:
> On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5
>
> <intermediatec...@gmail.com> 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 <intermediatec...@gmail.com> wrote:
> On 27 Sep, 19:44, Ron Rosenfeld <r...@nospam.net> wrote:
>
>
>
>
>
> > On Mon, 27 Sep 2010 09:36:35 -0700 (PDT), Johnnyboy5
>
> > <intermediatec...@gmail.com> 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