Message-ID: <3831B39F.83C3A0B5@fiastl.net> Date: Tue, 16 Nov 1999 13:43:05 -0600 From: "David J. Braden" Reply-to: tmy@fiastl.net Organization: TMY Research X-Mailer: Mozilla 4.6 (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 Subject: Re: counting words in area? References: <38319700.3671F490@norlec.com> Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Newsgroups: microsoft.public.excel.misc NNTP-Posting-Host: usr1ppp32.fiastl.net 216.15.165.166 Path: cppssbbsa01.microsoft.com!cppssbbsa05 Lines: 39 Xref: cppssbbsa01.microsoft.com microsoft.public.excel.misc:64366 Well, here's a different approach that covers the cells; it accounts for the possibility of a null cell,and for the possibility that there may have been more than one space between words. Regards Dave Braden Option Explicit Public function countWords(rng As range) As long dim lngAns As long dim rngCell As range for each rngCell In rng lngAns = lngAns + Wordcount(rngCell) next countWords = lngAns end function Private function Wordcount(c As range) As Integer dim iAns As Integer, iPos As Integer dim var As Variant var = application.Worksheetfunction.Trim(CStr(c)) if Vartype(var) <> vbNull then iPos = 1 do While iPos > 0 iPos = InStr(iPos + 1, var, " ") iAns = iAns + 1 loop end if Wordcount = iAns end function Geoff Taylor wrote: > > Greg > Here is a partial answer. > The following code counts the number of spaces in the activecell, then > adds one to infer the number of words. I'm afraid I don't have the skill > to make this cover a multiple cell selection. Hopefully someone can do > better.... > > Sub Wordcount() > dim Spacecount > dim Cellcount > Spacecount = 0 > for Cellcount = 1 to Len(activecell) > if Asc(Right(activecell.value, Cellcount)) = 32 then > Spacecount = Spacecount + 1 > end if > next Cellcount > next > msgbox Spacecount + 1 > end Sub > > HTH > Geoff