vba - 如何计算单元格中字符串出现的次数？

Question

我正在尝试计算该短语“SMM:”在 AJ 列中从第 2 行开始的每一行中出现的次数，然后将每行的值分配给从第 2 行开始的 BL 列。

Sub calculateamlp()


Dim charactercount As Integer
Dim rangeAG As Range
Dim cellCheck As Range
Dim f As Integer
f = 2
 Worksheets("pptsr").Activate

Set rangeAG2 = Range("BL2", Range("BL2").End(xlDown))
Set rangeAG = Range("Aj2", Range("Aj2").End(xlDown))
For Each cellCheck In rangeAG

        charactercount = Len(cellCheck) - Len(WorksheetFunction.Substitute(cellCheck, ":", ""))


        Worksheets("pptsr").Range("BL2" & f).Value = charactercount

   f = f + 1
Next cellCheck

End Sub

Answer 1

该函数通过使用子字符串来计算分割字符串中的元素数量来获得计数。

Function getStrOccurenceCount(Text As String, SubString As String)
    getStrOccurenceCount = UBound(Split(Text, SubString))
End Function

你可以这样修改你的代码

Worksheets("pptsr").Range("BL2" & f).Value = getStrOccurenceCount(cellCheck.Text, "SMM:")

以下是如何将 getStrOccurenceCount 与数组结合使用来提高效率。

Sub calculateamlp2()
    Const SUBSTRING As String = "SMM:"
    Dim rangeAG As Range
    Dim data As Variant
    Dim x As Long

    Set rangeAG = Range("AJ2", Range("AJ2").End(xlDown))

    data = rangeAG.Value

    For x = 1 To UBound(data)
        data(x, 1) = getStrOccurenceCount(CStr(data(x, 1)), SUBSTRING)
    Next

    rangeAG.EntireRow.Columns("BL").Value = data
End Sub

演示:样本数据999,999行，执行时间0.9375秒: