excel - 我需要一种算法来计算 Excel VBA 中以字母“a”和“A”开头的 M 列中的单词数

Question

在这里，我的代码只计算单词的数量，但我不知道该怎么做才能计算出 M 列中以字母“A”和“a”开头的单词

Sub CountWords()
    Dim xRg As Range
    Dim xRgEach As Range
    Dim xAddress As String       
    Dim xRgVal As String
    Dim xRgNum As Long
    Dim xNum As Long

    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Introduceti diapazonul:", "Selectare", xAddress, , , , , 8)

    If xRg Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

    If Application.WorksheetFunction.CountBlank(xRg) = xRg.Count Then
        MsgBox "Numarul de cuvinte este: 0", vbInformation, ""
        Exit Sub
    End If

    For Each xRgEach In xRg
        xRgVal = xRgEach.Value
        xRgVal = Application.WorksheetFunction.Trim(xRgVal)
        If xRgEach.Value <> "" Then
            xNum = Len(xRgVal) - Len(Replace(xRgVal, " ", "")) + 1
            xRgNum = xRgNum + xNum
        End If
    Next xRgEach

    MsgBox "Numarul de cuvinte: " & Format(xRgNum, "#,##0"), vbOKOnly, "Raspuns"

    Application.ScreenUpdating = True

End Sub

标签： excelvba

Answer 1

假设每个单元格包含一个单词，请使用：

Sub ACount()
    Dim i As Long, N As Long, Kount As Long
    Dim ch As String
    Kount = 0
    N = Cells(Rows.Count, "M").End(xlUp).Row

    For i = 1 To N
        ch = Left(Cells(i, "M").Value, 1)
        If ch = "a" Or ch = "A" Then Kount = Kount + 1
    Next i
    MsgBox Kount
End Sub

编辑＃1：

如果单元格可以包含多个单词（以空格分隔），则使用：

Sub ACount()
    Dim i As Long, N As Long, Kount As Long
    Dim ch As String
    Kount = 0
    N = Cells(Rows.Count, "M").End(xlUp).Row

    For i = 1 To N
        arr = Split(Cells(i, "M").Value, " ")
        For Each A In arr
            ch = Left(A, 1)
            If ch = "a" Or ch = "A" Then Kount = Kount + 1
        Next A
    Next i
    MsgBox Kount
End Sub