Tweaking a Macro to Count Words

[Guest]

Below is a macro offered by Chip P. that does a Word Count in Excel.

I would like to tweak it such that if runs only on the Cell I have selected.
It has been a while since I worked with macros and every tweak I have tried
produces an error message. Any help would be appreciated.

The underlying reason for my request is that I am finishing a Request for
Proposal response with a 100 word per answer limit. I suspect our customer is
using Excel to align question responses in columns from multiple vendors. I
want to make sure none of my responses exceed the limit but would rather not
count by hand or cut and paste each question (78) into MS-Word. My research
produced the macro below, but it counts the whole sheet.

Thanks in advance.

Paul
--------------

:

Excel doesn't have a built-in word counter. You'd have to loop
through each cell, determine how many words in each cell, and sum
these. For example,

Sub AAA()
Dim WordCount As Long
Dim Rng As Range
Dim S As String
Dim N As Long
For Each Rng In ActiveSheet.UsedRange.Cells
S = Rng.Text
N = 0
If S <> "" Then
N = Len(S) - Len(Replace(S, " ", "")) + 1
End If
WordCount = WordCount + N
Next Rng
MsgBox WordCount
End Sub

 

[Guest]

Delete the 'For' and 'Next' lines and change the assignment of S to
S=ActiveCell.Text

 

[Steve Yandl]

This is a slightly different approach but it should do what you want.

Sub WordsInCell()
Dim strInCell As String
Dim arrayWds As Variant
Dim intWdCount As Integer
strInCell = ActiveCell.Text
arrayWds = Split(strInCell)
intWdCount = UBound(arrayWds) + 1
MsgBox intWdCount
End Sub


Steve

 

[Guest]

Thanks Steve!

This is a slightly different approach but it should do what you want.

Sub WordsInCell()
Dim strInCell As String
Dim arrayWds As Variant
Dim intWdCount As Integer
strInCell = ActiveCell.Text
arrayWds = Split(strInCell)
intWdCount = UBound(arrayWds) + 1
MsgBox intWdCount
End Sub


Steve

 

[Guest]

Thanks Bp

Delete the 'For' and 'Next' lines and change the assignment of S to
S=ActiveCell.Text