Table of Contents
Year Occurrences
Below we will look at a program in Excel VBA that counts the number of year occurrences.
Situation:
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:
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.
