Auto Find with Counts 1

[steinfeld]

I am trying to create a Macro in MS Word that will allow me to get the counts of the text contained in each row of a selected column in a table.

Example.

At the top of the word document is a table that contains the following Table:

ID Description
TT-9 Shows three walls
XY-2 Shows two walls
RET-4 Shows floor color
WSD-22 Shows door operation

I select the first column ID and run the macro. The result I am looking for is a count of how many times each ID occurs in the remainder of the document.

Any help in this matter would greatly be appreciated.

 

[ThomasLafferty]

You could do this in Word, but why not paste the table into Excel and use the subtotals function on the data menu? Probably take all of 20 seconds.

Tom

Live once die twice; live twice die once.

 

[steinfeld]

Excellent suggestion, but I would like to keep it in word so I can distribute it to my fellow employees as an add-in. Many of them are not familiar with excel and are not able to make use of its full potential.

 

[xlhelp]

Hi, Steinfeld.

If do a google search for "count of specific text", "count of specific words", there are lots of different macros. I could paste one here, but you could get them just as easily.

Member AAA - Abolish Abused Abbreviations

 

[steinfeld]

I took a look at Google and this is the code I came up with. The ony problem I am having is when I am looking for the text yy-3 WORD considers it three words not one. It shows as yy - 3. Plus it takes a long time to parse through the entire document. Is there a faster method?

Sub reqCheck()

'I click anywhere in the first column of the values I am
'looking for.
'Selects the first column
Selection.Columns(1).Select

'Puts the entire document into a range for searching
Set myRange = ActiveDocument.Content

'Gets the total number of words in the selection
total = myRange.Words.Count

'Cycles through every cell in the column
For Each acell In Selection.Columns(1).Cells
acell.Select
a = Replace(Selection.Text, Chr(7), "")
a = Replace(a, Chr(13), "")

myCount = 0

'Cycles through every word in the document
For k = 1 To total
With myRange.Words(k)
If Trim(.Text) = a Then
myCount = myCount + 1
End If
End With
Next k

MsgBox (a & " " & myCount)
Next acell


End Sub

 

[fumei]

Nah nah nah. True, there are lots of ways of doing this, and there are lots of macros out there. Some of them way more complicated than they need to be. Here is a simple way. I am assuming that the ONLY text in the ID column is in fact the string you want to look for.

Sub BuildArray()
[COLOR=red]' this builds an array of the ID's to look for[/color red]
Dim strList() As String
Dim r As Word.Range
Dim oCell As Word.Cell
Dim i As Integer
Dim var
For Each oCell In Selection.Cells
    Set r = oCell.Range
    ReDim Preserve strList(i)
[COLOR=red]' have to strip off the TWO ASCII
' characters that are the end-of-cell markers
' Asc(13) and Asc(7)[/color red]
    strList(i) = Left(r.Text, Len(r.Text) - 2)
    Set r = Nothing
    i = i + 1
Next
[COLOR=red]' now use the array to call the word count Sub[/color red]
For var = 0 To UBound(strList())
    Call CountOfWord(strList(var))
Next
End Sub


Sub CountOfWord(strIn As String)
Dim iCount As Integer
With ActiveDocument.Content.Find
    Do While (.Execute(FindText:=strIn, Forward:=True) = True) = True
        iCount = iCount + 1
    Loop
End With
MsgBox "Counting the instance IN the table, there are " _
    & iCount & " instances of the word " & strIn
End Sub

Remember the count INCLUDES the one in the table. Adjust accordingly.

NOTE! NOTE! This does NOT have proper error trapping!!!!

1. You should always check if the Selection is in a table. If it is not, there is a run-time error. Use Selection.Information to check and exit the sub if it is not in the table.

2. The code will run as long as the Selection in IN a table. If you have not selected anything - just have the cursor in a cell, it will take the content of THAT cell and run with that. But it works if you select a whole column, or a single cell.

Gerry
My paintings and sculpture

 

[fumei]

Uh, also note that it does not really search/count "words". It searches/counts strings - so it can be "TT-9", or "Vice-President", or "yippee and hello there". It simply takes the content of the cell, and looks for matches of that content.

Gerry
My paintings and sculpture

 

[steinfeld]

Thanks Fumei