Table of Contents

Year Occurrences

Below we will look at a program in Excel VBA that counts the number of year occurrences.

Situation:

Year Occurrences in Excel VBA

Note: Dates are in US Format. Months first Days second. This type of format depends on your windows regional settings.

1. First we declare three variables of type Integer. One variable we call yearCount one variable we call yearAsk and one variable we call i.

Dim yearCount As Integer yearAsk As Integer i As Integer

2. We initialize yearCount with the value 0 and yearAsk with the value of cell C4.

yearCount = 0
yearAsk = Range(“C4”).Value

3. We start a For Next loop.

For i = 1 To 16

4. We now check each date and only if the year of the date equals the entered year in cell C4 we increment yearCount by 1. We use the Year function to get the year of a date.

If Year(Cells(i 1).Value) = yearAsk Then
yearCount = yearCount + 1
End If

5. Don’t forget to close the loop.

Next i

6. Finally we display the total year occurrences. We use the & operator to concatenate (join) two strings.

MsgBox yearCount & ” occurrences in year ” & yearAsk

7. Place your macro in a command button and test it.

Result:

Year Occurrences Result

Note: because we made yearAsk variable you can simply count the number of year occurrences of another year by entering another year in cell C4 and clicking on the command button again.

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog