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

Question

我想尝试在AJ列中从第二行开始计算每行中"SMM:"短语的出现次数，然后将每行的值分配给BL列，从第二行开始。请保留html标记。

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

- cookiemonster

---

你能贴出数据的截图吗？这样会帮助我给出答案！ - Jsleshem

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秒：

- user6432984

---

@Thomas Inzina，我也尝试了你的代码，在Sub calculateamlp2()和End Sub之间。但是我收到一个错误，说“未定义子程序或函数”，然后突出显示Sub calculateamlp2()。 - cookiemonster

听起来你的代码模块中没有getStrOccurenceCount函数，它要么在calculateamlp2模块中，要么在公共代码模块中。 - user6432984

Answer 2

要进行精确匹配，您应该使用"vbBinaryCompare"。如果您想将"smm:"与"SMM:"匹配，则应使用"vbTextCompare"。请尝试以下内容：

Sub calculateamlp()
Dim count As Long, i As Long, j As Long, rw As Long
Dim ws As Worksheet
Set ws = Worksheets("pptsr")
With ws
    rw = .Range("AJ" & .Rows.count).End(xlUp).Row
    For i = 2 To rw
        For j = 1 To Len(.Cells(i, "AJ"))
            If InStr(j, .Cells(i, "AJ"), "SMM:", vbTextCompare) Then
                count = count + 1
                j = InStr(j, .Cells(i, "AJ"), "SMM:", vbTextCompare)
            End If
        Next j
        .Cells(i, "BL") = count
        count = 0
    Next
End With
End Sub

- Tehscript

---

嗨@Tehscript，我把你的帖子从“SMM”改成了“SMM:”，我认为你在上面的帖子中漏掉了冒号。 - dwirony

@Tehscript，我尝试了这段代码，但是答案没有计入BL列。 - cookiemonster

@Tehscript，我刚注意到它更改了BL1的标题。 - cookiemonster

@Richmond 我刚试了一下，单元格BL1保持不变。这是不可能的，因为计数从2开始：For i = 2 To rw。也许其他地方已经改变了它？ - Tehscript

@Tehscript，你说得很对。在使用示例数据时，BL1保持不变。我现在离开工作簿了，所以我会在回到那里时再试一次。非常感谢你的帮助。 - cookiemonster