COUNTIF Loop for all sheets in workbook?

[Human_doing]

Hi all,

Can anyone please correct the below code, which is supposed to cycle through all worksheets in a workbook and place the number of occurences of the term 'C22' in row D in cell N2 of each sheet? However it doesn't actually seem to do anything in this format.

Thanks

Sub Looper()
 Dim Sh As Worksheet
    Dim LR As Long
    For Each Sh In ActiveWorkbook.Worksheets
    With Sh
    Dim x As Long
    x = Range("D" & Rows.Count).End(xlUp).Row
    Range("N2") = Application.WorksheetFunction.CountIf(Range("D2:D" & x), "C22")
 
    End With
 
    Next Sh
End Sub

 

[JackDanIce]

Try:

Sub Looper2()
 
Dim i As Long, j As Long
 
Application.ScreenUpdating = False
 
For i = 1 To Worksheets.Count
    With Sheets(i)
        j = .Range("D" & Rows.Count).End(xlUp).row
        .Range("N2") = Application.CountIf(.Range("D2:D" & j), "C22")
    End With
Next i
 
Sheets(1).Select
 
Application.ScreenUpdating = True
 
End Sub

 

[Ruddles]

Can anyone please correct the below code, which is supposed to cycle through all worksheets in a workbook...

To clarify, you were using Sh to cycle through the sheets okay and you opened a With block to simplify addressing your worksheet cells, but then you didn't use Sh to address the cells by placing a dot before the cell references.

It's a bit like running your finger down a list of telephone numbers you want to call but then always dialling your home number: you're moving down the list okay but you're not using the information from it.

Hopefully that explains?

 

[Human_doing]

Hi, thanks to you both yes that code works and I understand the initial issue, thanks

 

[JackDanIce]

And I didn't understand why inside a loop you placed:

For Each Sh In ActiveWorkbook.Worksheets
With Sh
Dim x as long
x = Range("D" & Rows.Count).End(xlUp).Row
.
.
.
Next i

You could have simply placed it outside the loop so it would not be "re-created" in each loop cycle i.e.

Dim x as long
 
For Each Sh In ActiveWorkbook.Worksheets
With Sh
x = Range("D" & Rows.Count).End(xlUp).Row
.
.
.
Next i

I'd also recommend indenting your code to identify blocks nested where as it will make readibility and in the long-run, identifying mistakes easier

 

[Ruddles]

Dim x as long

You could have simply placed it outside the loop so it would not be "re-created" in each loop cycle

Variables are created at compile-time, so x is only ever created once even if its declaration takes place inside a loop.

Proof:-

Sub xx()
 
Dim i
 
For i = 1 To 5
  Dim x
  MsgBox x
  x = x + 1
Next
 
End Sub

You're right though: it's best to place all declarations at the top of the sub or function where they can be located quickly and easily.

 

[Human_doing]

Thanks for your help everybody, yes I can understand why that code looked inefficient the way I wrote it and now it works fine so thanks for taking the time to help me!