Hi I need some kind formula to Countif (all the cells in whole
workbook / worksheet) for a certain text string.

johnnboy

On Sep 25, 4:33*pm, Johnnyboy5 <intermediatec...@gmail.com> wrote:
> Hi *I need some kind formula to Countif (all the cells in whole
> workbook / worksheet) for a certain text string.
>
> johnnboy

This will count all the cells in all the worksheets containing "hello"

Sub SuperCount()
Dim r As Range
SuperCounter = 0
s = "hello"
For Each sh In Sheets
sh.Activate
For Each r In ActiveSheet.UsedRange
If r.Value = s Then
SuperCounter = SuperCounter + 1
End If
Next
Next
MsgBox SuperCounter
End Sub

On 26 Sep, 01:09, James Ravenswood <james.ravensw...@gmail.com> wrote:
> On Sep 25, 4:33*pm, Johnnyboy5 <intermediatec...@gmail.com> wrote:
>
> > Hi *I need some kind formula to Countif (all the cells in whole
> > workbook / worksheet) for a certain text string.
>
> > johnnboy
>
> This will count all the cells in all the worksheets containing "hello"
>
> Sub SuperCount()
> Dim r As Range
> SuperCounter = 0
> s = "hello"
> For Each sh In Sheets
> * * sh.Activate
> * * For Each r In ActiveSheet.UsedRange
> * * * * * * If r.Value = s Then
> * * * * * * * * SuperCounter = SuperCounter + 1
> * * * * * * End If
> * * Next
> Next
> MsgBox SuperCounter
> End Sub

Thanks that works just great, I can see what I really need now, to
just count all the "hello" in the same column "n" in each worksheet
with the workbook.

Can it be done ?

thanks

Johnny

On Sep 26, 4:30*am, Johnnyboy5 <intermediatec...@gmail.com> wrote:
> On 26 Sep, 01:09, James Ravenswood <james.ravensw...@gmail.com> wrote:
>
>
>
>
>
> > On Sep 25, 4:33*pm, Johnnyboy5 <intermediatec...@gmail.com> wrote:
>
> > > Hi *I need some kind formula to Countif (all the cells in whole
> > > workbook / worksheet) for a certain text string.
>
> > > johnnboy
>
> > This will count all the cells in all the worksheets containing "hello"
>
> > Sub SuperCount()
> > Dim r As Range
> > SuperCounter = 0
> > s = "hello"
> > For Each sh In Sheets
> > * * sh.Activate
> > * * For Each r In ActiveSheet.UsedRange
> > * * * * * * If r.Value = s Then
> > * * * * * * * * SuperCounter = SuperCounter + 1
> > * * * * * * End If
> > * * Next
> > Next
> > MsgBox SuperCounter
> > End Sub
>
> Thanks *that works just great, I can see what I really need now, *to
> just count *all the "hello" in the same column "n" *in each worksheet
> with the workbook.
>
> Can it be done ?
>
> thanks
>
> Johnny- Hide quoted text -
>
> - 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