时间戳

首页 > 解决方案 > 在 vba excel 中，我试图做一个类似函数的计数，计算有多少适合 2 个 cirteria。一点经验

excel - 在 vba excel 中，我试图做一个类似函数的计数，计算有多少适合 2 个 cirteria。一点经验

问题描述

问题是当我F5时，它崩溃了。当我 F8 时，它在 for 循环中循环，但没有一个总数的值发生变化。我知道这一点，因为当我悬停时，该值仍然显示为 0。同样，我是新手，如果代码看起来很笨拙，请原谅。

Dim mycell As Range
Dim mycell_nw As Range
Dim myrange As Range
Dim myrange_nw As Range

Dim MaleTotal As Integer
Dim FemaleTotal As Integer
Dim UnTotal As Integer


Set myrange = Sheets("inforce").Range("ei:ei")
Set myrange_nw = Sheets("inforce").Range("ej:ej")


MaleTotal = 0
FemaleTotal = 0
UnTotal = 0

For Each mycell In myrange
For Each mycell_nw In myrange_nw
If mycell.Value = "M" And mycell_nw.Value = 0 Then
MaleTotal = MaleTotal + 1


ElseIf mycell.Value = "F" And mycell_nw.Value = 0 Then
FemaleTotal = FemaleTotal + 1


ElseIf mycell.Value = "U" And mycell_nw.Value = 0 Then
UnTotal = UnTotal + 1
End If
Next mycell_nw
Next mycell

标签： excelvbacount

解决方案

只需使用Application.CountIfs()

With Worksheets("inforce")
    Dim myrange As Range
    Set myrange = .Range("ei:ei")
    
    Dim myrange_nw As Range
    Set myrange_nw = .Range("ej:ej")
End With
    
Dim MaleTotal As Long
MaleTotal = Application.CountIfs(myrange, "M", myrange_nw, 0)

Dim FemaleTotal As Long
FemaleTotal = Application.CountIfs(myrange, "F", myrange_nw, 0)

Dim UnTotal As Long
UnTotal = Application.CountIfs(myrange, "U", myrange_nw, 0)

如果您不想使用 COUNTIFS 则迭代数组而不是范围：

With Worksheets("inforce")
    Dim myrange As Range
    Set myrange = Intersect(.UsedRange, .Range("ei:ej"))
    
    Dim myrange_array As Variant
    myrange_array = myrange.Value
End With

Dim MaleTotal As Long
MaleTotal = 0

Dim FemaleTotal As Long
FemaleTotal = 0

Dim UnTotal As Long
UnTotal = 0

Dim i As Long
For i = 1 To UBound(myrange_array, 1)
    If myrange_array(i, 2) = 0 Then
        If myrange_array(i, 1) = "M" Then MaleTotal = MaleTotal + 1
        If myrange_array(i, 1) = "F" Then FemaleTotal = FemaleTotal + 1
        If myrange_array(i, 1) = "U" Then UnTotal = UnTotal + 1
    End If
Next i

推荐阅读