excel - 如何使用VBA计算一个单元格中特定单词的总数，其他单元格也是如此？

我如何计算出现在一个单元格中的“alt”和“first”总数，对其他单元格也是如此，而忽略了该过程中的空单元格？例如，如果一个单元格具有first，first，alt，first，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

最佳答案

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

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

关于excel - 如何使用VBA计算一个单元格中特定单词的总数，其他单元格也是如此？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/60702011/