countif - in an entire workbook

  • Thread starter Thread starter Johnnyboy5
  • Start date Start date
J

Johnnyboy5

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

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

johnnboy
Click to expand...

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
 
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
Click to expand...

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
 
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 -
Click to expand...

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
 
Back
Top