[DeanH]

Word 2003 on XP.
I have a document that contains multiple tables, in each table the thrid
column contains "Y" or "N". I wish to count the occurances of both of these
characters, either 1) at the bottom of each table, and/or 2) total number of
both of these characters in all of the tables in the document.
Any help will be greatly appreciated.
DeanH

[Graham Mayor]

The following macro will total the Ys and Ns for each table and add them
with the total for the document at the end of the document.

Dim oTable As Table
Dim lYesDOC As Long
Dim lNoDOC As Long
Dim lYesTAB As Long
Dim lNoTAB As Long
Dim LastCell As Range
lYesDOC = 0
lNoDOC = 0
For i = 1 To ActiveDocument.Tables.Count
lYesTAB = 0
lNoTAB = 0
Set oTable = ActiveDocument.Tables(i)
For j = 1 To oTable.Rows.Count
If InStr(1, UCase(oTable.Cell(j, 3).Range), "Y") Then
lYesDOC = lYesDOC + 1
lYesTAB = lYesTAB + 1
End If
If InStr(1, UCase(oTable.Cell(j, 3).Range), "N") Then
lNoDOC = lNoDOC + 1
lNoTAB = lNoTAB + 1
End If
Next j
If i = 1 Then
ActiveDocument.Range.InsertAfter vbCr & _
"Sub totals:"
End If
ActiveDocument.Range.InsertAfter vbCr & _
"Table " & i & ": Yes - " & _
lYesTAB & " No - " & lNoTAB
Next i
ActiveDocument.Range.InsertAfter vbCr & _
"Totals: Yes - " & lYesDOC & " No - " & lNoDOC


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


DeanH wrote:
Word 2003 on XP.
I have a document that contains multiple tables, in each table the
thrid column contains "Y" or "N". I wish to count the occurances of
both of these characters, either 1) at the bottom of each table,
and/or 2) total number of both of these characters in all of the
tables in the document.
Any help will be greatly appreciated.
DeanH

[DeanH]

Many thanks Graham, unfortuantely this macro fails because, as I have just
found out, the customer's document contains a one-row one-cell table at the
start of the document which is not part of this exercise, i.e. does not
contain a third column and no Y's or N's.
Sorry.
In my search I have found an old posting (2007) from Doug Robbins, which
allows me to dictate which table to count from.
I have also defined that all of the customer's documents which need counting
will only have two tables, and the second one will always be the one to count.
Many thanks for your prompt reply and your assistance.
DeanH


"Graham Mayor" wrote:

The following macro will total the Ys and Ns for each table and add them
with the total for the document at the end of the document.

Dim oTable As Table
Dim lYesDOC As Long
Dim lNoDOC As Long
Dim lYesTAB As Long
Dim lNoTAB As Long
Dim LastCell As Range
lYesDOC = 0
lNoDOC = 0
For i = 1 To ActiveDocument.Tables.Count
lYesTAB = 0
lNoTAB = 0
Set oTable = ActiveDocument.Tables(i)
For j = 1 To oTable.Rows.Count
If InStr(1, UCase(oTable.Cell(j, 3).Range), "Y") Then
lYesDOC = lYesDOC + 1
lYesTAB = lYesTAB + 1
End If
If InStr(1, UCase(oTable.Cell(j, 3).Range), "N") Then
lNoDOC = lNoDOC + 1
lNoTAB = lNoTAB + 1
End If
Next j
If i = 1 Then
ActiveDocument.Range.InsertAfter vbCr & _
"Sub totals:"
End If
ActiveDocument.Range.InsertAfter vbCr & _
"Table " & i & ": Yes - " & _
lYesTAB & " No - " & lNoTAB
Next i
ActiveDocument.Range.InsertAfter vbCr & _
"Totals: Yes - " & lYesDOC & " No - " & lNoDOC


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


DeanH wrote:
Word 2003 on XP.
I have a document that contains multiple tables, in each table the
thrid column contains "Y" or "N". I wish to count the occurances of
both of these characters, either 1) at the bottom of each table,
and/or 2) total number of both of these characters in all of the
tables in the document.
Any help will be greatly appreciated.
DeanH

[DeanH]

Hi Graham, just to test I deleted the superfluous table at the start.
The macro works well, except it counts the "Y" and "N" in the header row. Doh!
Simpe to fix?
DeanH


"Graham Mayor" wrote:

The following macro will total the Ys and Ns for each table and add them
with the total for the document at the end of the document.

Dim oTable As Table
Dim lYesDOC As Long
Dim lNoDOC As Long
Dim lYesTAB As Long
Dim lNoTAB As Long
Dim LastCell As Range
lYesDOC = 0
lNoDOC = 0
For i = 1 To ActiveDocument.Tables.Count
lYesTAB = 0
lNoTAB = 0
Set oTable = ActiveDocument.Tables(i)
For j = 1 To oTable.Rows.Count
If InStr(1, UCase(oTable.Cell(j, 3).Range), "Y") Then
lYesDOC = lYesDOC + 1
lYesTAB = lYesTAB + 1
End If
If InStr(1, UCase(oTable.Cell(j, 3).Range), "N") Then
lNoDOC = lNoDOC + 1
lNoTAB = lNoTAB + 1
End If
Next j
If i = 1 Then
ActiveDocument.Range.InsertAfter vbCr & _
"Sub totals:"
End If
ActiveDocument.Range.InsertAfter vbCr & _
"Table " & i & ": Yes - " & _
lYesTAB & " No - " & lNoTAB
Next i
ActiveDocument.Range.InsertAfter vbCr & _
"Totals: Yes - " & lYesDOC & " No - " & lNoDOC


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


DeanH wrote:
Word 2003 on XP.
I have a document that contains multiple tables, in each table the
thrid column contains "Y" or "N". I wish to count the occurances of
both of these characters, either 1) at the bottom of each table,
and/or 2) total number of both of these characters in all of the
tables in the document.
Any help will be greatly appreciated.
DeanH

[Jay Freedman]

Hi Dean,

One way is simple but not foolproof: Assume that the table has one and only
one header row, and change the For loop to start in the second row:

For j = 2 To oTable.Rows.Count

The foolproof but not simple way is to locate the first row of the table
that is not a header row, and use that as the starting value of the For
loop:

Dim hr As Long
For hr = 1 To oTable.Rows.Count
If Not oTable.Rows(hr).HeadingFormat Then
Exit For
End If
Next

' Assume NOTHING! Maybe all rows are marked...
If hr oTable.Rows.Count Then
MsgBox "All rows marked as repeating header"
Exit Sub
End If

For j = hr To oTable.Rows.Count

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

DeanH wrote:
Hi Graham, just to test I deleted the superfluous table at the start.
The macro works well, except it counts the "Y" and "N" in the header
row. Doh! Simpe to fix?
DeanH


"Graham Mayor" wrote:

The following macro will total the Ys and Ns for each table and add
them with the total for the document at the end of the document.

Dim oTable As Table
Dim lYesDOC As Long
Dim lNoDOC As Long
Dim lYesTAB As Long
Dim lNoTAB As Long
Dim LastCell As Range
lYesDOC = 0
lNoDOC = 0
For i = 1 To ActiveDocument.Tables.Count
lYesTAB = 0
lNoTAB = 0
Set oTable = ActiveDocument.Tables(i)
For j = 1 To oTable.Rows.Count
If InStr(1, UCase(oTable.Cell(j, 3).Range), "Y") Then
lYesDOC = lYesDOC + 1
lYesTAB = lYesTAB + 1
End If
If InStr(1, UCase(oTable.Cell(j, 3).Range), "N") Then
lNoDOC = lNoDOC + 1
lNoTAB = lNoTAB + 1
End If
Next j
If i = 1 Then
ActiveDocument.Range.InsertAfter vbCr & _
"Sub totals:"
End If
ActiveDocument.Range.InsertAfter vbCr & _
"Table " & i & ": Yes - " & _
lYesTAB & " No - " & lNoTAB
Next i
ActiveDocument.Range.InsertAfter vbCr & _
"Totals: Yes - " & lYesDOC & " No - " & lNoDOC


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


DeanH wrote:
Word 2003 on XP.
I have a document that contains multiple tables, in each table the
thrid column contains "Y" or "N". I wish to count the occurances of
both of these characters, either 1) at the bottom of each table,
and/or 2) total number of both of these characters in all of the
tables in the document.
Any help will be greatly appreciated.
DeanH

[Graham Mayor]

It is simple to fix if we know what you are dealing with.

If you want to ignore the first table change the line

For i = 1 To ActiveDocument.Tables.Count
to
For i = 2 To ActiveDocument.Tables.Count

however the following version will skip any table with 1 or 2 columns

If one or more of the tables have header rows then the following will also
ignore any cell that contains anything other than Y or y or N or n

Dim oTable As Table
Dim lYesDOC As Long
Dim lNoDOC As Long
Dim lYesTAB As Long
Dim lNoTAB As Long
Dim oCell As Range
lYesDOC = 0
lNoDOC = 0
For i = 1 To ActiveDocument.Tables.Count
If ActiveDocument.Tables(i).Columns.Count 2 Then
lYesTAB = 0
lNoTAB = 0
Set oTable = ActiveDocument.Tables(i)
For j = 1 To oTable.Rows.Count
Set oCell = oTable.Cell(j, 3).Range
oCell.End = oCell.End - 1
If UCase(oCell.Text) = "Y" Then
lYesDOC = lYesDOC + 1
lYesTAB = lYesTAB + 1
End If
If UCase(oCell.Text) = "N" Then
lNoDOC = lNoDOC + 1
lNoTAB = lNoTAB + 1
End If
Next j
If i = 1 Then
ActiveDocument.Range.InsertAfter vbCr & _
"Sub totals:"
End If
ActiveDocument.Range.InsertAfter vbCr & _
"Table " & i & ": Yes - " & _
lYesTAB & " No - " & lNoTAB
End If
Next i
ActiveDocument.Range.InsertAfter vbCr & _
"Totals: Yes - " & lYesDOC & " No - " & lNoDOC


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



DeanH wrote:
Hi Graham, just to test I deleted the superfluous table at the start.
The macro works well, except it counts the "Y" and "N" in the header
row. Doh! Simpe to fix?
DeanH


"Graham Mayor" wrote:

The following macro will total the Ys and Ns for each table and add
them with the total for the document at the end of the document.

Dim oTable As Table
Dim lYesDOC As Long
Dim lNoDOC As Long
Dim lYesTAB As Long
Dim lNoTAB As Long
Dim LastCell As Range
lYesDOC = 0
lNoDOC = 0
For i = 1 To ActiveDocument.Tables.Count
lYesTAB = 0
lNoTAB = 0
Set oTable = ActiveDocument.Tables(i)
For j = 1 To oTable.Rows.Count
If InStr(1, UCase(oTable.Cell(j, 3).Range), "Y") Then
lYesDOC = lYesDOC + 1
lYesTAB = lYesTAB + 1
End If
If InStr(1, UCase(oTable.Cell(j, 3).Range), "N") Then
lNoDOC = lNoDOC + 1
lNoTAB = lNoTAB + 1
End If
Next j
If i = 1 Then
ActiveDocument.Range.InsertAfter vbCr & _
"Sub totals:"
End If
ActiveDocument.Range.InsertAfter vbCr & _
"Table " & i & ": Yes - " & _
lYesTAB & " No - " & lNoTAB
Next i
ActiveDocument.Range.InsertAfter vbCr & _
"Totals: Yes - " & lYesDOC & " No - " & lNoDOC


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


DeanH wrote:
Word 2003 on XP.
I have a document that contains multiple tables, in each table the
thrid column contains "Y" or "N". I wish to count the occurances of
both of these characters, either 1) at the bottom of each table,
and/or 2) total number of both of these characters in all of the
tables in the document.
Any help will be greatly appreciated.
DeanH

[DeanH]

Many thanks, Graham, apologies for the confusion, the customer not knowing
what its right and left hands are doing! :-(

The For i = 2 should work very well.
I will have a play tomorrow when I have time.

Jay, many thanks for the contribution as well.

DeanH has left the building...
;-)


"Graham Mayor" wrote:

It is simple to fix if we know what you are dealing with.

If you want to ignore the first table change the line

For i = 1 To ActiveDocument.Tables.Count
to
For i = 2 To ActiveDocument.Tables.Count

however the following version will skip any table with 1 or 2 columns

If one or more of the tables have header rows then the following will also
ignore any cell that contains anything other than Y or y or N or n

Dim oTable As Table
Dim lYesDOC As Long
Dim lNoDOC As Long
Dim lYesTAB As Long
Dim lNoTAB As Long
Dim oCell As Range
lYesDOC = 0
lNoDOC = 0
For i = 1 To ActiveDocument.Tables.Count
If ActiveDocument.Tables(i).Columns.Count 2 Then
lYesTAB = 0
lNoTAB = 0
Set oTable = ActiveDocument.Tables(i)
For j = 1 To oTable.Rows.Count
Set oCell = oTable.Cell(j, 3).Range
oCell.End = oCell.End - 1
If UCase(oCell.Text) = "Y" Then
lYesDOC = lYesDOC + 1
lYesTAB = lYesTAB + 1
End If
If UCase(oCell.Text) = "N" Then
lNoDOC = lNoDOC + 1
lNoTAB = lNoTAB + 1
End If
Next j
If i = 1 Then
ActiveDocument.Range.InsertAfter vbCr & _
"Sub totals:"
End If
ActiveDocument.Range.InsertAfter vbCr & _
"Table " & i & ": Yes - " & _
lYesTAB & " No - " & lNoTAB
End If
Next i
ActiveDocument.Range.InsertAfter vbCr & _
"Totals: Yes - " & lYesDOC & " No - " & lNoDOC


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



DeanH wrote:
Hi Graham, just to test I deleted the superfluous table at the start.
The macro works well, except it counts the "Y" and "N" in the header
row. Doh! Simpe to fix?
DeanH


"Graham Mayor" wrote:

The following macro will total the Ys and Ns for each table and add
them with the total for the document at the end of the document.

Dim oTable As Table
Dim lYesDOC As Long
Dim lNoDOC As Long
Dim lYesTAB As Long
Dim lNoTAB As Long
Dim LastCell As Range
lYesDOC = 0
lNoDOC = 0
For i = 1 To ActiveDocument.Tables.Count
lYesTAB = 0
lNoTAB = 0
Set oTable = ActiveDocument.Tables(i)
For j = 1 To oTable.Rows.Count
If InStr(1, UCase(oTable.Cell(j, 3).Range), "Y") Then
lYesDOC = lYesDOC + 1
lYesTAB = lYesTAB + 1
End If
If InStr(1, UCase(oTable.Cell(j, 3).Range), "N") Then
lNoDOC = lNoDOC + 1
lNoTAB = lNoTAB + 1
End If
Next j
If i = 1 Then
ActiveDocument.Range.InsertAfter vbCr & _
"Sub totals:"
End If
ActiveDocument.Range.InsertAfter vbCr & _
"Table " & i & ": Yes - " & _
lYesTAB & " No - " & lNoTAB
Next i
ActiveDocument.Range.InsertAfter vbCr & _
"Totals: Yes - " & lYesDOC & " No - " & lNoDOC


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


DeanH wrote:
Word 2003 on XP.
I have a document that contains multiple tables, in each table the
thrid column contains "Y" or "N". I wish to count the occurances of
both of these characters, either 1) at the bottom of each table,
and/or 2) total number of both of these characters in all of the
tables in the document.
Any help will be greatly appreciated.
DeanH