COUNTIF in VBA Across Multiple Sheets

[kajohnson9]

I have a function using COUNTIF where I want to count the number of occurrances of a number (input value) across multiple sheets. I thought this would be straightforward but I can't get it to execute. All I get is a beep when I try. Any help you can give me is appreciated. The code is as follows and it is called from a cell as =countbyyear(F5) where F5 contains a number, like 2024.

Function CountByYear(myYear)
Dim s$, ws As Worksheet
Dim EachCount As Integer
CountByYear = 0
For Each ws In Worksheets
    If UCase$(ws.Name) <> "TOC" Then
        EachCount = WorksheetFunction.CountIf(Range("C3:C200"), myYear)
        CountByYear = CountByYear + EachCount
    End If
Next
End Function

 

[Peter_SSs]

The range needs to be referenced to each worksheet or else each time it will use that range on the active sheet. Try this instead

Function CountByYear(myYear) As Long
  Dim ws As Worksheet
  Dim EachCount As Long
 
  For Each ws In Worksheets
      If UCase$(ws.Name) <> "TOC" Then
          EachCount = WorksheetFunction.CountIf(ws.Range("C3:C200"), myYear)
          CountByYear = CountByYear + EachCount
      End If
  Next
End Function

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)