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.

 

[kvsrinivasamurthy]

Apply break point to yhis line.
If ws.Name <> "TOC" Then
Execute the formula.
Use function key F8 to go step by step..

 

[kajohnson99]

I don't know how to use built-in formulas to accomplish this. As I mentioned, when I enter =SumValuesByYear(TOC!F8) in cell G8 in the sheet TOC, I get a #NAME error. So it isn't recognizing the function name. That's what I don't understand.
K

 

[Alex Blakenburg]

Did you put it in a standard module ?

 

[Cubist]

I don't know how to use built-in formulas to accomplish this

Something like this?

Book2
	A	B	C
1	Year	2024
2	Count	2
3
4			1/1/25
5			1/2/24
6			2/6/24
7			6/5/25
8			8/4/23
Sheet4

Cell Formulas
Range	Formula
B2	B2	=COUNTIFS(C:C,">="&DATE(B1,1,1),C:C,"<="&DATE(B1,12,31))

 

[Alex Blakenburg]

@Cubist, we are reading the requirement as being to return a single value totalling a "count across all sheets".
@kajohnson99 are the sheets in a block ie can you select the first sheet and then holding the shift key down select the last sheet to select all the sheets to be summed ?

 

[kvsrinivasamurthy]

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
This function can be used like other functions in excel.
Save file as .xlsm

Then apply formula in the cell as I have given in post#2.