countif - in an entire workbook
 

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

johnnboy

countif - in an entire workbook
 

On Sep 25, 4:33*pm, Johnnyboy5 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

countif - in an entire workbook
 

On 26 Sep, 01:09, James Ravenswood wrote:
On Sep 25, 4:33*pm, Johnnyboy5 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

countif - in an entire workbook
 

On Sep 26, 4:30*am, Johnnyboy5 wrote:
On 26 Sep, 01:09, James Ravenswood wrote:





On Sep 25, 4:33*pm, Johnnyboy5 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