Counting Text Occurrence In A Multi-row Range   

I've been working to find a solution to counting the cells in a Word
table that are in a multirow range, and have an "X" as text. (Table is
13 rows X 32 columns)  Using the code below, I get a count of all of
the "X"s in rows 2 and 3.  What I want to do is count the "X"s in the
range (in this case, 2, 31 thru 3, 2).  Can someone please explain why
this code counts the complete rows, and give a possible solution?
TIA
Sub DoCount()
Dim tmpCount As Integer, CurrCol As Integer, CurrRow As Integer, MyStr
As String, MyRange As Range
        tmpCount = 0
        MyStr = "x"
    Set MyTable = ActiveDocument.Tables(1)
    Set MyRange = ActiveDocument.Range(Start:=MyTable.Cell(2,
31).Range.Start, _
        End:=MyTable.Cell(3, 2).Range.End)
        MyRange.Find.Execute FindText:=MyStr, Forward:=False
        While MyRange.Find.Found = True
            tmpCount = tmpCount + 1
            MyRange.Find.Execute
        Wend
MsgBox ("There are " & tmpCount & " finds")
End Sub

date: Thu, 15 Nov 2007 10:48:45 -0800 (PST)   author:   unknown

Re: Counting Text Occurrence In A Multi-row Range   

The problem with your code is that, despite setting the .Start and .End of 
MyRange where you want them, the range actually includes the entire rows. To 
see this, insert the line
    MyRange.Select
between the Set MyRange statement and the first .Execute, and use F8 to step 
through the code one line at a time. You'll see it highlight all of rows 2 
and 3. I would consider this a bug, except that it's been in every version 
of VBA. I think it has to do with including the end-of-row marker to the 
right of row 2, although that doesn't explain why all of row 3 is also 
included.

One solution is to step through the cells with the .Next method, checking 
each time to see whether you're still in the desired area of the table and 
then examining the current cell's contents:

Sub DoCount2()
    Dim myCell As Cell, tmpCount As Integer
    tmpCount = 0
    Set myCell = ActiveDocument.Tables(1).Cell(2, 31)
    While (Not myCell Is Nothing) And _
        ((myCell.RowIndex = 2) Or _
         (myCell.RowIndex = 3 And myCell.ColumnIndex < 3))
        If myCell.Range.Characters(1) = "x" Then
            tmpCount = tmpCount + 1
        End If
        Set myCell = myCell.Next
    Wend
    MsgBox ("There are " & tmpCount & " finds")
End Sub

The condition of the While statement says to enter the loop "If the current 
cell is not Nothing (which it will be if you run off the end of the table), 
and it's in row 2 -or- it's in the first two cells of row 3".

The .Next method works its way from left to right across a row and then 
jumps to the first cell of the next row, just like tabbing through the 
table.

This would be terribly inefficient if the table had thousands of cells to 
check and only a few of them contained an "x". In that case, I'd go back to 
the Find method, but set the range initially to the entire set of rows 
(since that's what you'll get anyway) and examine the row and column indexes 
of each hit to see whether it's within the desired area. As soon as a hit is 
beyond the end of the desired area, you can exit from the loop.

-- 
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so 
all may benefit.

simplysewing@gmail.com wrote:
> I've been working to find a solution to counting the cells in a Word
> table that are in a multirow range, and have an "X" as text. (Table is
> 13 rows X 32 columns)  Using the code below, I get a count of all of
> the "X"s in rows 2 and 3.  What I want to do is count the "X"s in the
> range (in this case, 2, 31 thru 3, 2).  Can someone please explain why
> this code counts the complete rows, and give a possible solution?
> TIA
> Sub DoCount()
> Dim tmpCount As Integer, CurrCol As Integer, CurrRow As Integer, MyStr
> As String, MyRange As Range
>        tmpCount = 0
>        MyStr = "x"
>    Set MyTable = ActiveDocument.Tables(1)
>    Set MyRange = ActiveDocument.Range(Start:=MyTable.Cell(2,
> 31).Range.Start, _
>        End:=MyTable.Cell(3, 2).Range.End)
>        MyRange.Find.Execute FindText:=MyStr, Forward:=False
>        While MyRange.Find.Found = True
>            tmpCount = tmpCount + 1
>            MyRange.Find.Execute
>        Wend
> MsgBox ("There are " & tmpCount & " finds")
> End Sub

date: Thu, 15 Nov 2007 14:54:53 -0500   author:   Jay Freedman

Re: Counting Text Occurrence In A Multi-row Range   

Jay, thanks for your solution which does work around my multi-row
range dilemma.  When I used that code with variables in a test module,
and assigned integers to the variables, everything worked great!
However, when I inserted the code into my macro along with the code
for all procedures, etc., it resorted to searching the entire next row
again!  The problem HAS TO BE something with the variables that report
the cell positions, but I can't see the solution.
I've included the modified code from my macro.  The BlackHit  variable
reports whether the current cell has an "X".  If not, BlackHit =
False, and the code would then check to find out if any cells with an
"X" were passed through.  If any were, tmpCount would count them, and
they would be used later to move ahead the number of cells reported by
tmpCount.  RngRow1 and RngCol1 are the variables for the position
before the move to the next cell, and RngRow2 and RngCol2 report the
current cell.  The variables appear to be reporting properly when I
step through the code.  Anything jump out at you?  (I would have
included the entire macro, but it's about 7 pages!)  Again, thanks for
your past assistance, and any more you can offer.

    If BlackHit = False And RngRow1 > 0 Then
        tmpCount = 0
    Set myCell = ActiveDocument.Tables(1).Cell(RngRow1, RngCol1)
    While Not (myCell Is Nothing) And _
        ((myCell.RowIndex = RngRow1) Or _
         (myCell.RowIndex = RngRow2 And myCell.ColumnIndex < RngCol2))
        If myCell.Range.Characters(1) = "X" Then
            tmpCount = tmpCount + 1
        End If
        Set myCell = myCell.Next
        myCell.Select
    Wend
    End If

date: Mon, 19 Nov 2007 16:10:22 -0800 (PST)   author:   unknown

Re: Counting Text Occurrence In A Multi-row Range   

That code does look OK to me. I assume that as you step through the
code, the MyCell.Select causes the proper set of cells to be
highlighted, and no more after that.

Use the Find command to look at all occurrences in the entire macro of
the variable name tmpCount, to make sure it isn't being reassigned a
different value elsewhere.

Use the Watch window to keep an eye on all the variables involved in
this section of code as you step through, and look for unexplained
changes.

On Mon, 19 Nov 2007 16:10:22 -0800 (PST), simplysewing@gmail.com
wrote:

>Jay, thanks for your solution which does work around my multi-row
>range dilemma.  When I used that code with variables in a test module,
>and assigned integers to the variables, everything worked great!
>However, when I inserted the code into my macro along with the code
>for all procedures, etc., it resorted to searching the entire next row
>again!  The problem HAS TO BE something with the variables that report
>the cell positions, but I can't see the solution.
>I've included the modified code from my macro.  The BlackHit  variable
>reports whether the current cell has an "X".  If not, BlackHit =
>False, and the code would then check to find out if any cells with an
>"X" were passed through.  If any were, tmpCount would count them, and
>they would be used later to move ahead the number of cells reported by
>tmpCount.  RngRow1 and RngCol1 are the variables for the position
>before the move to the next cell, and RngRow2 and RngCol2 report the
>current cell.  The variables appear to be reporting properly when I
>step through the code.  Anything jump out at you?  (I would have
>included the entire macro, but it's about 7 pages!)  Again, thanks for
>your past assistance, and any more you can offer.
>
>    If BlackHit = False And RngRow1 > 0 Then
>        tmpCount = 0
>    Set myCell = ActiveDocument.Tables(1).Cell(RngRow1, RngCol1)
>    While Not (myCell Is Nothing) And _
>        ((myCell.RowIndex = RngRow1) Or _
>         (myCell.RowIndex = RngRow2 And myCell.ColumnIndex < RngCol2))
>        If myCell.Range.Characters(1) = "X" Then
>            tmpCount = tmpCount + 1
>        End If
>        Set myCell = myCell.Next
>        myCell.Select
>    Wend
>    End If

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

date: Mon, 19 Nov 2007 20:24:39 -0500   author:   Jay Freedman