excel - 如何计算一个单元格中特定单词的总数并使用 VBA 对其他单元格做同样的事情？

Question

如何计算一个单元格中出现的“alt”和“first”的总数，并对其他单元格也做同样的事情，同时忽略过程中的空单元格？例如，如果一个单元格有 first、first、alt、first、first、first，它应该给我 firstcounter = 5(其中 firstcounter 是 first 的总计数)和 altcounter= 1(altcounter 是 alt 的总计数)。之后，我可以使用找到的 firstcounter 和 altcounter 的值将它们连接成一个字符串，如 B 列中所示，形式为“first-”& firstcounter、“alt-”& altcounter。

Dim ia As Long
Dim lastrow2 As Long
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets(1)
lastrow2 = ws1.Range("A" & ws1.Rows.count).End(xlUp).Row
For ia = 2 To lastrow2
  Dim arr() As Variant
    ' Split the string to an array
arr = Split(ws1.Cells(ia, "A"), ",").Value
'what should i do after split

Answer 1

将以下内容输入代码模块...

Function CountWords$(r)
    Dim a&, f&, w
    For Each w In Split(r, ",")
        If w = "alt" Then a = a + 1
        If w = "first" Then f = f + 1
    Next
    If (a + f) Then CountWords = "first-" & f & ",alt-" & a
End Function

然后在 B2 单元格中输入这个公式:

=CountWords(A2)

现在根据需要向下复制它。

---

更新

要在 VBA 中使用上述函数而不在工作表中输入公式，您可以这样做...

Sub Cena()
    Dim i&, v
    With [a2:a8]
        v = .Value2
        For i = 1 To UBound(v)
            v(i, 1) = CountWords(v(i, 1))
        Next
        .Offset(, 1) = v
    End With
End Sub

Function CountWords$(r)
    Dim a&, f&, w
    For Each w In Split(r, ",")
        If w = "alt" Then a = a + 1
        If w = "first" Then f = f + 1
    Next
    If (a + f) Then CountWords = "first-" & f & ",alt-" & a
End Function

---

更新#2

针对您在评论中提出的问题，您可以改用此变体...

Sub Cena()
    Dim i&, v
    With [a2].Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1)
        v = .Value2
        For i = 1 To UBound(v)
            v(i, 1) = CountWords(v(i, 1))
        Next
        .Cells = v
    End With
End Sub

Function CountWords$(r)
    Dim a&, f&, w
    For Each w In Split(r, ",")
        If w = "alt" Then a = a + 1
        If w = "first" Then f = f + 1
    Next
    If (a + f) Then CountWords = "first-" & f & ",alt-" & a
End Function