[Solved] count number of occurances of a given year in column c across multiple worksheets

[kajohnson9]

I have aworkbook with multiple worksheets. I have various date data in Column C. On the first workshhet, I have a list of years (2022,2023,2024,etc) I want to count the total number of times a particular year (based on the year in a cell in the first sheet) across all worksheets. And I want to call this function from a particular cell in shett 1.
I have tried the following code and I don't see the problem with it. Can someone please help?

Public Function Sum_values_by_year()
'declare a variable
Dim ws As Worksheet
Dim WS_Count As Integer
Dim syear As Integer

WS_Count = ActiveWorkbook.Worksheets.count

syear = ws.Range("F" & ActiveCell.Row) 'This gives me an error 91

sumyears = 0

' Loop through each worksheet
For j = 1 To WS_Count
Worksheets(j).Activate
'sum values by year using the For Loop
For i = 1 To 200
If Year(ws.Cells(i, 3)) = syear Then
sumyears = sumyears + ws.Cells(i, 3).Value
End If
Next i
Next j

ws.Range("G" & ActiveCell.Row) = sumyears
End Function

 

[shamimarita]

Hello kajohnson9,

Your VBA function has a few issues causing the error:

• Undefined Variable: The variable ws is not initialized or set to any worksheet.
• Error in syear Assignment: ws.Range("F" & ActiveCell.Row) references ws, which isn't defined, leading to "Error 91."

Update the function as follows:

Public Function Sum_values_by_year() 
    Dim ws As Worksheet 
    Dim syear As Integer 
    Dim sumyears As Long 
    Dim cell As Range 
    
    ' Use the active sheet for reference
    Set ws = ActiveSheet 
    syear = ws.Range("F" & ActiveCell.Row).Value 

    sumyears = 0 

    ' Loop through each worksheet 
    For Each ws In ActiveWorkbook.Worksheets 
        For Each cell In ws.Columns(3).Cells 
            If Year(cell.Value) = syear Then 
                sumyears = sumyears + 1 
            End If 
        Next cell 
    Next ws 

    Sum_values_by_year = sumyears 
End Function

• Ensures ws references each worksheet in the loop.
• Fixes the year reference by using the correct range.
• It uses Sum_values_by_year to return the value.

 

[kajohnson9]

Shamima, thank you for your reply. I learned quite a bit from your example. I made your recommended changes, but when I execute the function, I get a #VALUE error. I'm not understanding how to correct that error. All help is appreciated.
Keith

 

[shamimarita]

Hello kajohnson9,

The #VALUE! error typically occurs if the function tries to process invalid or incompatible data. Here are steps to resolve it:

Verify Data in Column C: Ensure all cells in Column C contain valid dates that Excel recognizes. Non-date entries can cause errors.
Error Handling in Code: Update the function to skip invalid values:

For Each cell In ws.Columns(3).Cells
    If IsDate(cell.Value) And Year(cell.Value) = syear Then
        sumyears = sumyears + 1
    End If
Next cell

Check the ActiveCell: Ensure the ActiveCell used to retrieve the year in F has a valid numeric value.