VBA Code help to count no. of occurrences per month

Hello All,

I am getting gray hair over this one. With the aid of a Macro, I was able to generate the following code to count the amount of incidents by month:

Sub IncidentsByMonth()
    'open sheet 3
    Sheets("sheet3").Select
    
    
    'Select 1st row
    Rows("1:1").Select
    Selection.NumberFormat = "mmm-yy"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1/1/2009"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:AK1"), Type:=xlFillDefault
    Range("A1:AK1").Select
    'Range("A1").Select
    
    
    Rows("2:2").Select
    Selection.NumberFormat = "0"
    'January 09
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(infraData!RC[4]:R[64999]C[4]>=39814),--(infraData!RC[4]:R[64999]C[4]<=39844.99))"
    
    'February 09 etc...
    Range("b2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(infraData!RC[3]:R[64999]C[3]>=39845),--(infraData!RC[3]:R[64999]C[3]<=39872.99))"
        
    Range("c2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(infraData!RC[2]:R[64999]C[2]>=39873),--(infraData!RC[2]:R[64999]C[2]<=39903.99))"
        
    Range("d2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(infraData!RC[1]:R[64999]C[1]>=39904),--(infraData!RC[1]:R[64999]C[1]<=39933.99))"
        
    Range("e2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(infraData!RC[0]:R[64999]C[0]>=39934),--(infraData!RC[0]:R[64999]C[0]<=39964.99))"
    
    Range("f2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(infraData!RC[-1]:R[64999]C[-1]>=39965),--(infraData!RC[-1]:R[64999]C[-1]<=39994.99))"
    
    Range("g2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(infraData!RC[-2]:R[64999]C[-2]>=39995),--(infraData!RC[-2]:R[64999]C[-2]<=40025.99))"
        
    Range("h2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(infraData!RC[-3]:R[64999]C[-3]>=40026),--(infraData!RC[-3]:R[64999]C[-3]<=40056.99))"
        
    Range("i2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(infraData!RC[-4]:R[64999]C[-4]>=40057),--(infraData!RC[-4]:R[64999]C[-4]<=40086.99))"
    
End Sub

At first I was having issues with the SUMPRODUCT statements, as I did realize I had to de-increment the RC references. OK. I figured that one out and it is working now, i.e. I can get the code to do what it is supposed.

My main issue now is that I realized that I would virtually have to add a new line of code for every new month in the future, i.e. do the next 5 years in advance, so that I do not have to constantly update the code.

I was wondering if anyone new of a more elegant way to solve the issue?

Column E contains information on the Log Date of Support cases. I have formatted the column to display, as follows "Mar-98".

I need code, so that excel will automatically generate 2 rows in sheet3. 1st row containing month and year, denoted as above and the 2nd row containing the sum of occurrences.

Ideally the code will automatically recognize the (new) month and year and automatically generate the according header in the cell above. I've attached the table for your reference:

I highly appreciate any help in advance. Please let me know, if you require any additional information.

Thx,

Soph.