Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Count of a unique letter in a column
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 |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
Count of a unique letter in a column
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 |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
Count of a unique letter in a column
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 |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
Count of a unique letter in a column
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 |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
Count of a unique letter in a column
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 |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
Count of a unique letter in a column
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 |
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
Count of a unique letter in a column
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count table rows with specific value in a column | Tables | |||
count how often a letter appears within text | Tables | |||
how do i count unique items in word table? | Tables | |||
how do I sort a column by the last letter of words | Microsoft Word Help | |||
Count non-blank cells in a table column | New Users |