software

Chip,

This is very cool! Is there a way to set this up for quot;active cellquot; only? I
am doing an Request for Proposal response with a 100 word per answer limit. I
suspect our customer is using Excel to align question responses in columns
form 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 word.

Thanks in advance.

Paul

quot;Chip Pearsonquot; wrote:

gt; Excel doesn't have a built-in word counter. You'd have to loop
gt; through each cell, determine how many words in each cell, and sum
gt; these. For example,
gt;
gt; Sub AAA()
gt; Dim WordCount As Long
gt; Dim Rng As Range
gt; Dim S As String
gt; Dim N As Long
gt; For Each Rng In ActiveSheet.UsedRange.Cells
gt; S = Rng.Text
gt; N = 0
gt; If S lt;gt; quot;quot; Then
gt; N = Len(S) - Len(Replace(S, quot; quot;, quot;quot;)) 1
gt; End If
gt; WordCount = WordCount N
gt; Next Rng
gt; MsgBox WordCount
gt; End Sub
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt; quot;Nadinequot; gt; wrote in message
gt; ...
gt; gt;I am translating and need a word count.
gt;
gt;
gt;

You could use a worksheet formula in a cell right next to your input cell to see
the results for that cell:

Something like:

=IF(A1=quot;quot;,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),quot; quot;,quot;quot;)) 1)

====
But you could change one line in Chip's routine to work against the activecell:

For Each Rng In ActiveSheet.UsedRange.Cells
becomes
For Each Rng In ActiveCell.CellsPGiessler wrote:
gt;
gt; Chip,
gt;
gt; This is very cool! Is there a way to set this up for quot;active cellquot; only? I
gt; am doing an Request for Proposal response with a 100 word per answer limit. I
gt; suspect our customer is using Excel to align question responses in columns
gt; form multiple vendors. I want to make sure none of my responses exceed the
gt; limit but would rather not count by hand or cut and paste each question (78)
gt; into word.
gt;
gt; Thanks in advance.
gt;
gt; Paul
gt;
gt; quot;Chip Pearsonquot; wrote:
gt;
gt; gt; Excel doesn't have a built-in word counter. You'd have to loop
gt; gt; through each cell, determine how many words in each cell, and sum
gt; gt; these. For example,
gt; gt;
gt; gt; Sub AAA()
gt; gt; Dim WordCount As Long
gt; gt; Dim Rng As Range
gt; gt; Dim S As String
gt; gt; Dim N As Long
gt; gt; For Each Rng In ActiveSheet.UsedRange.Cells
gt; gt; S = Rng.Text
gt; gt; N = 0
gt; gt; If S lt;gt; quot;quot; Then
gt; gt; N = Len(S) - Len(Replace(S, quot; quot;, quot;quot;)) 1
gt; gt; End If
gt; gt; WordCount = WordCount N
gt; gt; Next Rng
gt; gt; MsgBox WordCount
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Cordially,
gt; gt; Chip Pearson
gt; gt; Microsoft MVP - Excel
gt; gt; Pearson Software Consulting, LLC
gt; gt; www.cpearson.com
gt; gt;
gt; gt;
gt; gt; quot;Nadinequot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I am translating and need a word count.
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson