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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

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