countif - in an entire workbook

[Johnnyboy5]

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

johnnboy

 

[James Ravenswood]

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

 

[Johnnyboy5]

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

 

[Don Guillett Excel MVP]

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