Row 9 within each column contains a date that is formatted 'Custom' (see below):
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".
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:
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.
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".
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.