Count the Number of Date Entries per Month with VBA Macro

[skirdZ]

Hi, I’m new to VBA and I’m trying to modify the code below.
I have a large workbook with separate sheets assigned to different products. Eachsheet has numerous date entries. I’d like to have the macro count the number ofentries per month in “Sheet1” with the output in “Sheet11”.

The code below works if the dates and the output are in thesame active worksheet, however, when I try to assign “a” to Sheet1 and “itm” to“Sheet11” I get a compile error.

“E2” is equal to “2019”

Any help will be very much appreciated.


Sub MonthlyCount()

Dim a As Variant, itm As Variant
Dim b(1 To 12, 1 To 1) As Long, myYear As Long
a = Sheet1.Range("A23", Range("A" &Rows.Count).End(xlUp)).Value
myYear = Range("E2").Value

For Each itm In a
IfSheet11.Year(itm) = myYear Then b(Month(itm), 1) = b(Month(itm), 1) + 1

Next itm
Range("C2").Resize(12).Value = b

End Sub

 

[DanteAmor]

Hi, I’m new to VBA and I’m trying to modify the code below.
I have a large workbook with separate sheets assigned to different products. Eachsheet has numerous date entries. I’d like to have the macro count the number ofentries per month in “Sheet1” with the output in “Sheet11”.

The code below works if the dates and the output are in thesame active worksheet, however, when I try to assign “a” to Sheet1 and “itm” to“Sheet11” I get a compile error.

“E2” is equal to “2019”

Any help will be very much appreciated.


Sub MonthlyCount()

Dim a As Variant, itm As Variant
Dim b(1 To 12, 1 To 1) As Long, myYear As Long
a = Sheet1.Range("A23", Range("A" &Rows.Count).End(xlUp)).Value
myYear = Range("E2").Value

For Each itm In a
If Sheet11.Year(itm) = myYear Then b(Month(itm), 1) = b(Month(itm), 1) + 1

Next itm
Range("C2").Resize(12).Value = b

End Sub

Try this.

You should only refer to the sheet when it comes to a range.

Sub MonthlyCount()


    Dim a As Variant, itm As Variant
    Dim b(1 To 12, 1 To 1) As Long, myYear As Long
    
    a = [COLOR=#008000]Sheet1[/COLOR].Range("A23", Range("A" & Rows.Count).End(xlUp)).Value
    myYear = [COLOR=#008000]Sheet1[/COLOR].Range("E2").Value
    
    For Each itm In a
        If Year(itm) = myYear Then b(Month(itm), 1) = b(Month(itm), 1) + 1
    Next itm
    [COLOR=#008000]Sheet11[/COLOR].Range("C2").Resize(12).Value = b


End Sub

 

[skirdZ]

Thank you.

I think this is close to working.

I get a run time error '1004' "application-defined or object-defined error" for "a".
If I remove "Sheet1" the code works, but I need the data from another sheet.


a = Sheet1.Range("A23", Range("A" & Rows.Count).End(xlUp)).Value</pre>

 

[DanteAmor]

Thank you.

I think this is close to working.

I get a run time error '1004' "application-defined or object-defined error" for "a".
If I remove "Sheet1" the code works, but I need the data from another sheet.


a = Sheet1.Range("A23", Range("A" & Rows.Count).End(xlUp)).Value

We go back to my scoop:
You should only refer to the sheet when it comes to a range.

Sub MonthlyCount()


    Dim a As Variant, itm As Variant
    Dim b(1 To 12, 1 To 1) As Long, myYear As Long
    
    a = Sheet1.Range("A23", [COLOR=#008000][B]Sheet1[/B][/COLOR].Range("A" & Rows.Count).End(xlUp)).Value
    myYear = Sheet1.Range("E2").Value
    
    For Each itm In a
        If Year(itm) = myYear Then b(Month(itm), 1) = b(Month(itm), 1) + 1
    Next itm
    Sheet11.Range("C2").Resize(12).Value = b


End Sub

 

[skirdZ]

That worked! Thank you so much!

 

[DanteAmor]

That worked! Thank you so much!

I'm glad to help you. Thanks for the feedback.