Calculate the Number of Date Entries Corresponding to the Month and Year of Todays Date

[skirdZ]

Hi,

I have a large workbook comprised of sheets dedicated to a specific product. Each product sheet has a list of dates.

I’m trying to create a macro that would tally the number of date entries corresponding to the month and year of todays date. So if today is “13 June 2019” the macro should tally the number of date entries in the month of June 2019.

This_day = Sheet11.Range("Q2").Value references a cell with the “=TODAY()” formula.
I get a run time error 13 “Type mismatch” when I run the code. I’m also not sure if

If Month(k) And Year(k) is the correct way to pull out the month and year corresponding to “TODAY”.
Any help is very much appreciated!


Sub LotsClosedThisMonth()

Dim Last_Row As Variant
Dim k As Variant
Dim j As Variant
Dim This_day As Long

This_day = Sheet11.Range("Q2").Value
Last_Row = Sheet1.Range("O8", Sheet1.Range("O" & Rows.Count).End(xlUp)).Value

j = 0

For k = 2 To Last_Row​

If Month(k) And Year(k) = This_day Then j = j + 1
​

Next k
​

Sheet11.Range("C3").Value = j


End Sub

 

[Joe4]

For k = 2 To Last_Row
If Month(k) And Year(k) = This_day Then j = j + 1

k is just a number (counter) here, not a date. It looks like you are using it to iterate through row numbers.
To get the month out of that row number, you would need something like:

[COLOR=#000000][FONT=Calibri]Month(Sheet1.Range("O" & k))[/FONT][/COLOR]

Also, this calculation appears a bit curious to me:

[COLOR=#000000][FONT=Calibri]Last_Row = Sheet1.Range("O8", Sheet1.Range("O" & Rows.Count).End(xlUp)).Value[/FONT][/COLOR]

This is not finding the last row in column O in Sheet1, it is finding the value in the last populated cell column O on Sheet1.
If you just actually want the last populated row number in column O, use:

[COLOR=#000000][FONT=Calibri]Last_Row = Sheet1.Range("O" & Rows.Count).End(xlUp).Row[/FONT][/COLOR]

 

[WaterGypsy]

Re: VBA Macro to Calculate the Number of Date Entries Corresponding to the Month and Year of Today’s Date

could you use EOMONTH to calculate the start and end date of the month and then countif to see how many of the dates are between those dates .... may be more efficient than vba

 

[Joe4]

Re: VBA Macro to Calculate the Number of Date Entries Corresponding to the Month and Year of Today’s Date

I have merged your two threads together. Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will typically be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it. Please be patient and do not attempt to post the question again.

 

[skirdZ]

Re: VBA Macro to Calculate the Number of Date Entries Corresponding to the Month and Year of Today’s Date

Thanks Joe4.
There was a delay with my first post so I thought it wasn't submitted. I'll be more patient next time.

I actually figured it out so I thought I'd share the solution.


Sub LotsClosedCount()

Dim a As Variant, itm As Variant
Dim b(1 To 12, 1 To 1) As Long, myYear As Long

 

[skirdZ]

Re: VBA Macro to Calculate the Number of Date Entries Corresponding to the Month and Year of Today’s Date

Sorry I'm struggling to post.


Sub LotsClosedCount()

Dim a As Variant, itm As Variant
Dim b(1 To 12, 1 To 1) As Long, myYear As Long


Dim mthNum As Integer
Dim yearNum As Integer

currDate = Sheet11.Range("H2").Value
mthNum = Month(currDate)
yearNum = Year(currDate)


a = Sheet1.Range("O8", Sheet1.Range("O" & Rows.Count).End(xlUp)).Value

For Each itm In a
If Year(itm) = yearNum And Month(itm) = mthNum Then b(Month(itm), 1) = b(Month(itm), 1) + 1
Next itm
Range("T3").Resize(12).Value = b


Dim j As Variant
Dim k As Variant

j = 1
k = Sheet11.Range("T3:T14").Value

For Each j In k
If j <> 0 Then i = j

Next

Sheet11.Range("B4") = i


Range("T3:T14").clear


If Range("B4") = "" Then Range("B4") = 0

End Sub