Search code examples
excelvbacountif

How to use COUNTIF in VBA?

I am trying to use VBA to count the number of dates in a range. My range expands columns and rows, I am trying to find the last row and the last column but cannot seem to get the desired results I want.

How can I use the last row and the last column in my COUNTIF function?

Here is the code I have so far:

Sub count_Month()
Dim ws As Worksheet
Dim LastCol As Long
Dim count As Long
Dim lastrow As Long

lastrow = Sheet3.Range("M" & Rows.count).End(xlUp).Row
Set ws = Sheets("clientmenu")

    If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
        LastCol = 1
    Else
        LastCol = ws.Cells.Find(What:="*", _
                After:=ws.Range("A8"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
    End If

    With Application.WorksheetFunction
        count = .CountIfs(Sheet2.Range("M8" & LastCol), ">=" & Sheet2.Range("R25"), Sheet2.Range("M8" & LastCol), "<=" & Sheet2.Range("R26"))

    End With
        Debug.Print count
End Sub

Screenshot of my range Range I have so far

Solution 
  • Sub count_Month()
Dim ws As Worksheet
Dim LastCol As Long, LastRow As Long, count as Long, rng as Range

LastRow = Sheet3.Range("M" & Sheet3.Rows.count).End(xlUp).Row
Set ws = Sheets("clientmenu")

If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
    LastCol = 1
Else
    LastCol = ws.Cells.Find(What:="*", _
            After:=ws.Range("A8"), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Column
End If

With Sheet2
    Set rng = .Range(.Cells(8, 13), .Cells(LastRow, LastCol))
End With
count = Application.WorksheetFunction.CountIfs(rng, ">=" & Sheet2.Range("R25"), _
        rng, "<=" & Sheet2.Range("R26"))
Debug.Print count

End Sub