excel - 如何插入条件“如果包含”以在 VBA 中搜索特定字母？

Question

我想计算参数 CA、CU 和 CH 在如下所示的 excel 中出现了多少次：

我尝试使用以下代码，但由于单元格不仅包含我正在搜索的参数，因此它不起作用：

Sub ContarOV()

    Dim cont As Variant
    Dim sumaCA As Variant
    Dim sumaCU As Variant
    Dim sumaCH As Variant

    sumaCA = 0
    sumaCU = 0
    sumaCH = 0

    For cont = 3 To 12
        If Cells(cont, 2) = ("CA") Then
            sumaCA = sumaCA + 1
        End If
        If Cells(cont, 2) = ("CU") Then
             sumaCU = sumaCU + 1
        End If
        If Cells(cont, 2) = ("CH") Then
            sumaCH = sumaCH + 1
        End If
    Next cont

End Sub

标签： excelvbafiltercountconditional-statements

Answer 1

根据@BigBen，我会尽量避免任何迭代。以下选项之一怎么样（假设您的数据来自A2:A?）：

Sub Test()

Dim lr As Long, x As Long
Dim arr As Variant
Dim rng As Range

With Sheet1 'Change according to your sheets CodeName

    'Get last used row
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row

    'Get data into memory for method 1
    arr = Application.Transpose(.Range("A2:A" & lr).Value)

    'Create range object for method 2
    Set rng = .Range("A2:A" & lr)

    'Method 1: Count values with FILTER
    Debug.Print UBound(Filter(arr, "CA")) + 1
    Debug.Print UBound(Filter(arr, "CU")) + 1
    Debug.Print UBound(Filter(arr, "CH")) + 1

    'Method 2: Count values with COUNTIF
    Debug.Print WorksheetFunction.CountIf(rng, "CA*")
    Debug.Print WorksheetFunction.CountIf(rng, "CU*")
    Debug.Print WorksheetFunction.CountIf(rng, "CH*")

End With

End Sub

顺便说一句，在这种情况下，我会给sumaCA你的其他变量一个有意义的数据类型。Long