Count # of times a specific month occurs within a range...

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
457
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Row 9 within each column contains a date that is formatted 'Custom' (see below):
ROW9.JPG

Directly below the cell in Row 9, Row10 shows the same date but these cells are formatted "special" using the "mmm-yyyy" option. The date is shown as "Jan-22".
STATS.JPG


For my example, within the range, there are 8 dates that occur in January. That is the number that I am trying to get as my return.

Here is my Code:
VBA Code:
Private Sub cmdSTATS_Click()
'
Dim cP As Range
Dim PCol As Long
    PCol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
  '
Dim Lst2
Dim Lst3
Dim Lst4
Dim Lst5
'
e = 0
'
Lst2 = Format(DateAdd("m", -0, Date), "mmm-yy")
Lst3 = Format(DateAdd("m", -1, Date), "mmm-yy")
Lst4 = Format(DateAdd("m", -2, Date), "mmm-yy")
Lst5 = Format(DateAdd("m", -3, Date), "mmm-yy")
MsgBox Lst2
'
    For Each cP In Range(Cells(10, 14), Cells(10, PCol)).SpecialCells(xlCellTypeVisible)
        If cP.value = Lst2 Then
            e = e + 1
    End If
    Next
'
MsgBox e
'
End Sub

This code returns '0' for the count of Lst2 which is "Jan-22" and it should be 8.

I cant seem to find what is wrong with thecode, but I suspect it has something to do with how the cells are formatted and they are not being recognized as "Lst2" ("Jan-22"). THanks for any help.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
You might consider replacing this line...

VBA Code:
If cP.Value = Lst2 Then

...with this line...

VBA Code:
If Month(cP.Value) & Year(cP.Value) = Month(Lst2) & Year(Lst2) Then

Cheers,

Tony
 
Upvote 0
Solution
You might consider replacing this line...

VBA Code:
If cP.Value = Lst2 Then

...with this line...

VBA Code:
If Month(cP.Value) & Year(cP.Value) = Month(Lst2) & Year(Lst2) Then

Cheers,

Tony
Thank you, Tony. That worked perfectly.
 
Upvote 0
The updated code works... but only partly.

It finds the correct number of Feb values and January values... but it finds 0 for the months of December and November (which should be 14 and 16, respectively)

So because the same exact code finds 2 of the months correctly, but not the other 2, then I am assuming that there must be something wrong with the cells themselves and maybe those months in those cells are not formatted the same as the ones that actually work.

I have confirmed that the variables are correct (by verifying with 'MsgBox' to show me what each 'Lst' actually is) and have confirmed that everything in the column where the code is looking is all formatted the same and is correct. (if I take one of the december dates and I change it to january, it will then find it, but not if I keep it december... ??) strange.

My code:
VBA Code:
Private Sub CommandButton1_Click()
Dim cP As Range

Dim PCol As Long
    PCol = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column

Dim rCol As Long
    rCol = ActiveSheet.UsedRange.Rows.Count
Dim ws As Worksheet
Set ws = Worksheets("CHANGE_LOG")
        ws.AutoFilter.ShowAllData
'
MsgBox rCol
'
Dim Lst2    ' February 2022
Dim Lst3    ' January 2022
Dim Lst4    ' December 2021
Dim Lst5    ' November 2021
'
e = 0
f = 0
g = 0
h = 0
'
Lst2 = Format(DateAdd("m", -0, Date), "mmm-yy")
Lst3 = Format(DateAdd("m", -1, Date), "mmm-yy")
Lst4 = Format(DateAdd("m", -2, Date), "mmm-yy")
Lst5 = Format(DateAdd("m", -3, Date), "mmm-yy")
'
MsgBox Lst2
MsgBox Lst3
MsgBox Lst4
MsgBox Lst5
'
With ws
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    ActiveSheet.Range(Cells(14, 1), Cells(rCol, 16)).Rows.Hidden = False
    ActiveSheet.Range(Cells(14, 1), Cells(rCol, 16)).AutoFilter Field:=4, Criteria1:="ADDED"
End With

For Each cP In Range(Cells(14, 16), Cells(rCol, 16)).SpecialCells(xlCellTypeVisible)
    If Month(cP.value) & Year(cP.value) = Month(Lst2) & Year(Lst2) Then e = e + 1   ' Should find 0 february dates (it does.)
    If Month(cP.value) & Year(cP.value) = Month(Lst3) & Year(Lst3) Then f = f + 1   ' Should find 8 january dates (it does.)
    If Month(cP.value) & Year(cP.value) = Month(Lst4) & Year(Lst4) Then g = g + 1   ' Should find 14 december dates... (finds 0)
    If Month(cP.value) & Year(cP.value) = Month(Lst5) & Year(Lst5) Then h = h + 1   ' Should find 16 november dates... (finds 0)
Next cP
'
    MsgBox "February " & e
    MsgBox "Janruary " & f
    MsgBox "December " & g
    MsgBox "November " & h
'
End Sub
 
Upvote 0
FWIW here is the spreadsheet and how the data appears on it:
Capture.JPG
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top