vba - 计算所有工作表上不同范围内的总计

Question

我正在尝试总计 3 个。批准、拒绝和等待

工作簿中的每个工作表的同一列中都包含此信息。

到目前为止我尝试过的内容如下，进行了各种调整，但似乎算错了，而且我似乎无法掌握如何做到正确。

    Sub test()
Dim WS As Worksheet, rng1 As Range, rng2 As Range, rng3 As Range, T1 As Integer, T2 As Integer, T3 As Integer
Dim Lastrow As Long
On Error Resume Next

Set rng1 = Range(("B1"), Range("B" & Rows.Count).End(xlUp))
Set rng2 = Range(("C1"), Range("C" & Rows.Count).End(xlUp))
Set rng3 = Range(("D1"), Range("D" & Rows.Count).End(xlUp))


T1 = 0
T2 = 0
T3 = 0

For Each WS In ActiveWorkbook.Worksheets
    With WS
        For Each cell In rng1
            If cell.Value = "1" Then T1 = T1 + 1
        Next
        For Each cell In rng2
            If Cells.Value = "1" Then T2 = T2 + 1
        Next
        For Each cell In rng3
            If Cells.Value = "1" Then T3 = T3 + 1
        Next
    End With
Next

MsgBox "Godkendte: " & T1 & "--- Afviste: " & T2 & "--- Afventer: " & T3

End Sub

Answer 1

您的代码可以稍微简化。如果您发现自己在末尾编写带有数字的变量名称，那么最好使用数组

Sub test()
    Dim ws As Worksheet
    Dim v(1 To 3) As Variant

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            v(1) = v(1) + Application.WorksheetFunction.CountIf(.Range(.Range("B1"), .Range("B" & .Rows.Count).End(xlUp)), 1)
            v(2) = v(2) + Application.WorksheetFunction.CountIf(.Range(.Range("C1"), .Range("C" & .Rows.Count).End(xlUp)), 1)
            v(3) = v(3) + Application.WorksheetFunction.CountIf(.Range(.Range("D1"), .Range("D" & .Rows.Count).End(xlUp)), 1)
        End With
    Next

    MsgBox "Godkendte: " & v(1) & "--- Afviste: " & v(2) & "--- Afventer: " & v(3)
End Sub