Finding Number of Times a Specific Year Value is Used

[kajohnson99]

I'm a novice at Excel VBA. I have a workbook with multiple sheets. In column C on each sheet, there may be a date value. I want to search each sheet and count the number of times the year value of the date is the same as the input year. The input year is in a specific cell on sheet TOC. I am calling the function from a cell in TOC.
I seem to go into an infinite loop with the system continually beeping at me. My function code is attached. some one tell what I'm doing wrong?

Public Function SumValuesByYear()

Dim ws As Worksheet
Dim SumYears As Long
Dim cell As Range
Dim TargetYear As Range
Dim nbCells As Integer
Dim sYear As Long


Set ws = ActiveSheet
sYear = Year(ActiveCell.Value)
'sYear = "2022"
SumYears = 0
' Loop through each worksheet
For Each ws In ActiveWorkbook.Worksheets
SumYears = 0
If ws.Name <> "TOC" Then
nbCells = 0
'sum values by year using the For Loop
For Each cell In ws.Columns(3).Cells
' For nbCells = 1 To 35
If IsDate(cell.Value) Then
If Year(cell.Value) = sYear Then
SumYears = SumYears + 1
End If
End If
' Next nbCells
Next cell
End If
Next ws
SumValuesByYear = SumYears
End Function

 

[kvsrinivasamurthy]

Try .
In any cell enter the function, If H3 in TOC sheet is the cell contains required year.

=SumValuesByYear('TOC'!H3)

Public Function SumValuesByYear(Cel As Range)

Dim ws As Worksheet
Dim SumYears As Long
Dim sYear As Long
Dim Lr&, T&

sYear = Year(Cel.Value)
SumYears = 0
' Loop through each worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "TOC" Then
'sum values by year using the For Loop
Lr = 0
Lr = ws.Range("C" & Rows.Count).End(xlUp).Row
For T = 1 To Lr
If IsDate(ws.Cells(T, 3).Value) Then
If Year(ws.Cells(T, 3).Value) = sYear Then
SumYears = SumYears + 1
End If
End If
Next T
End If
Next ws
SumValuesByYear = SumYears
End Function

 

[kajohnson99]

Thanks for your response. I have entered your code as is. Please correct me if I'm wrong. I included it in the Modules section of my project. My concern is when I enter =SumValuesByYear(TOC!F5) in a cell, I get #NAME? error. And when I compile the function, I get no error but I can't step into the code to execute it step by step.
Can you help me to determine what I'm doing wrong?
Thanks,
Keith Johnson

 

[Cubist]

Is there a reason for VBA? Built-in formulas can accomplish the same.