excelvbaxsl-stylesheet

Counting number of words of each column in VBA

I'am trying to implement a code so that all the number of words from each cell in a column can be calculated and displayed in a cell just next to them.

I have written this code, but it shows Complie Error: Loop without Do, where as I'am having it.

Sub Command()
    total_words = 1
    Dim ans_length As Integer
    Dim start_point As Integer

    Range("N3").Select

    Do Until ActiveCell.Value = ""

        ans_length = Len(ActiveCell.Offset(0, 13).Value)
        For start_point = 1 To ans_length
            If (Mid(ans_length, start_point, 1)) = " " Then
            total_words = total_words + 1
            End If
        ActiveCell.Offset(0, 12).Value = total_words
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

say i have this content:

      Col1                      Col2
The only way to do multi   |      6
line comments in VB        |      4
the only option you have   |      5
is the single              |      3

here i have col2 by default and writing VBA code for col2

Solution

  • This UDF approach would be an easier option ... well ... in my opinion anyway.

    Public Function CountWords(ByVal strText As String) As Long
    Application.Volatile
    CountWords = UBound(Split(strText, " ")) + 1
End Function

    ... you can then use that in any cell.

    enter image description here

    If you want to go with your original approach, you were missing a NEXT.

    Sub Command()
    total_words = 1

    Dim ans_length As Integer
    Dim start_point As Integer

    Range("N3").Select

    Do Until ActiveCell.Value = ""
        ans_length = Len(ActiveCell.Offset(0, 13).Value)

        For start_point = 1 To ans_length
            If (Mid(ans_length, start_point, 1)) = " " Then
                total_words = total_words + 1
            End If
        Next start_point

        ActiveCell.Offset(0, 12).Value = total_words
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub