Counting Text Occurrence In A Multi-row Range

[simplysewing]

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

 

[Jay Freedman]

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
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[simplysewing]

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

 

[Jay Freedman]

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.