Using CountIf function in VBA to count number of entries with a specific Month and Year (ignoring Day)

Question

I'm fairly new at VBA. Using Excel 2007 VBA, I'm trying to count the number of entries in the "H" column of the "WOMade" worksheet that have a date in a specific month and year (ignoring the day), but all the methods I've tried aren't working.

Among other ideas, I've tried:

WorksheetFunction.CountIf(Sheets("WOMade").Columns("H:H"), "June-15")

And

WorksheetFunction.CountIf(Format(Sheets("WOMade").Columns("H:H"), "mmyyyy"), "062015")

And

WorksheetFunction.CountIf(Sheets("WOMade").Columns("H:H"), "June/" & "/2015)

And

WorksheetFunction.CountIfs(Sheets("WOMade").Columns("H:H"), ">=" 6/1/2015, Sheets("WOMade").Columns("H:H"), < 7/1/2015)

Any ideas?

Answer 1

I would think that the last example was the closest.

dim i as long
with Sheets("WOMade")
    i = WorksheetFunction.CountIfs(.Columns("H:H"), ">="  & dateserial(2015, 6, 1), .Columns("H:H"), "<" & dateserial(2015, 7, 1))
    'or,
    i = WorksheetFunction.CountIfs(.Columns("H:H"), ">="  & datevalue("6/1/2015"), .Columns("H:H"), "<" & datevalue("7/1/2015"))
    'or,
    i = WorksheetFunction.CountIfs(.Columns("H:H"), ">=6/1/2015", .Columns("H:H"), "<7/1/2015")
end with

Answer 2

Sub Test() Sheets("WOMade").Select

    SetYear = 2015
    SetMonth = 1
    Count = 0

    i = 1
    Do While Cells(i, 8) <> ""
        ' Subtract for TRUE as it is stored as -1
        Count = Count - (Year(Cells(i, 8)) = SetYear And Month(Cells(i, 8)) = SetMonth)

        i = i + 1
    Loop

    MsgBox Count

End Sub

Answer 3

Public Function CountOfYearMonth(r As Range, y As Integer, m As Integer) As Long

    Set r = Intersect(r, r.Parent.UsedRange) 'Don't iterate over more than you have to

    'Technically n is not necessary, you can just use CountOfYearMonth
    Dim n As Long, _
        c As Range, _
        d As Date

    For Each c In r.Cells        
        If IsDate(c.Value) Then 'Does the cell contain a date?
            d = CDate(c.Value) 'Convert, if so
            If month(d) = m And year(d) = y Then 'Check year and month
                n = n + 1 'If match, increment total
            End If
        End If
    Next c

    CountOfYearMonth = n 'Assign return value
End Function