时间戳

首页 > 解决方案 > 如何在 VBA 中使用 COUNTIF？

excel - 如何在 VBA 中使用 COUNTIF？

问题描述

我正在尝试使用 VBA 来计算一个范围内的日期数。我的范围扩大了列和行，我试图找到最后一行和最后一列，但似乎无法获得我想要的结果。

如何使用COUNTIF函数中的最后一行和最后一列？

这是我到目前为止的代码：

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

我的范围截图 到目前为止我的范围

标签： excelvbacountif

解决方案

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

推荐阅读