Tweaking a Macro to Count Words
 

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
--------------

"Chip Pearson" wrote:

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

Tweaking a Macro to Count Words
 

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


"PGiessler" wrote:

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
--------------

"Chip Pearson" wrote:

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

Tweaking a Macro to Count Words
 

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


"PGiessler" wrote in message
...
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
--------------

"Chip Pearson" wrote:

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

Tweaking a Macro to Count Words
 

Thanks Steve!

"Steve Yandl" wrote:

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


"PGiessler" wrote in message
...
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
--------------

"Chip Pearson" wrote:

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

Tweaking a Macro to Count Words
 

Thanks Bp

"bpeltzer" wrote:

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


"PGiessler" wrote:

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
--------------

"Chip Pearson" wrote:

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