Public Function WordCount

[micglick]

Why does this Public Function not return the correct word count and instead provides a value of "0":

Public Function CountWords(NumRange As Range) As Long
    Dim rCell As Range, lCount As Long
    For Each rCell In NumRange
        lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), "", "")) + 1
    Next rCell
    CountWords = lCount
End Function

 

[mohadin]

Try change to

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1

 

[Herakles]

Try this.

This line needed a space between the first set of double quotes.

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1

Public Function CountWords(NumRange As Range) As Long
   Dim rCell As Range, lCount As Long
   For Each rCell In NumRange
       lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1
   Next rCell
   CountWords = lCount
End Function

 

[Phuoc]

Or try

Public Function CountWords(NumRange As Range) As Long
Dim rCell As Range, lCount As Long
For Each rCell In NumRange
lCount = lCount + UBound(Split(Application.Trim(rCell.Value), " ")) + 1
Next rCell
CountWords = lCount
End Function

 

[micglick]

Try change to

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1

Thank you!

 

[mohadin]

You are very welcome
And thank you for the feedback
Be happy and safe