Tweaking a Macro to Count Words

P

PGiessler

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
 
B

bpeltzer

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

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
 
P

PGiessler

Thanks Steve!

Steve Yandl said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top