VBA to search each row of a word table column

Marrick13 · #1 11-16-2014, 03:09 PM

I am trying to finish a macro that searches a selected Word column for user-entered text. I have a module that works, except that I need the search code to perform a 'MatchWholeWord' query and count the hits. The only way I know to do that is with a 'Do While .Execute' statement, but I'm having trouble getting it to search the column properly.

The code is have is in the the attached 'Count_Column_Text' file. I was using 'If InStr(oCell.Range.Text, TargetText) > 0 Then i = i + 1' (commented out in the attachment) and it found each occurrence of the search text. But it doesn't match the whole word, so if my search text phrase includes a word that exists by itself in several cells, those single words are included in the count.

Can someone advise how to do a proper match whole word query with a counter?

macropod · #2 11-16-2014, 04:45 PM

Try:

Code:

Sub Count_Column_Text()
Dim TargetText As String, StrMsg As String, i As Long, j As Long, Rng As Range
With Selection
  If .Information(wdWithInTable) = False Then
    MsgBox "The cursor must be within a table cell.", , "Cursor outside table"
    Exit Sub
  Else
    TargetText = InputBox("Enter text to search. " & _
      "This macro will search the column you have " & _
      "selected for this text and count how many " & _
      "times it appears in that column. Do you want to continue?", _
      "Enter text to find and count")
      For i = 1 To .Cells.Count
        Set Rng = .Cells(i).Range
        With .Cells(i).Range
          With .Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = TargetText
            .Forward = True
            .Wrap = wdFindStop
            .Format = False
            .MatchCase = False
            .MatchWholeWord = True
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
            .Execute
          End With
          Do While .Find.Found
            If .Duplicate.InRange(Rng) Then
              j = j + 1
              .Collapse wdCollapseEnd
              .Find.Execute
            Else
              Exit Do
            End If
          Loop
        End With
      Next
    Select Case j
      Case 0: StrMsg = "The text " & TargetText & " was not found in the selected column "
      Case 1: StrMsg = "There is 1 occurrence of the text " & TargetText & " in the selected column "
      Case Else: StrMsg = "There are " & j & " occurrences of the text '" & TargetText & "' in the selected column "
    End Select
    MsgBox StrMsg & .Cells(1).ColumnIndex & ".", , "Text search in column"
  End If
End With
End Sub

Marrick13 · #3 11-16-2014, 05:42 PM

Thanks, Paul. This is what happens when I test the code against a test table:

The word "scanned" appears 29 times in one column of the table; your code says there is 1 occurrence

The phrase "TO BE SCANNED" appears 21 times in one column of the table; your code says there is 1 occurrence

Two other searches for words that occur once each in the same and other columns
had no hits with this code.

Not sure why this is - does the 'If .Duplicate.InRange(Rng)' code have something to do with it? I need a count of all search hits.

Marrick13 · #4 11-16-2014, 05:49 PM

Tested again and the search on "scanned" produced not hits. I just realized that your code does not identify the column to be searched. I had
'CursorCol = Selection.Cells(1).ColumnIndex' in my original code that sets the column where the pointer is as the column to search, and 'For Each oCell In oTbl.Columns(CursorCol).Cells' to loop through that column. Now I don't know what the code you revised looks at.

macropod · #5 11-16-2014, 08:09 PM

The code I posted assumes the cells to be tested have been selected. It can therefore work with whatever block of cells you select, be that a single cell, part or all of one or more rows & columns, etc.

To work with all of whatever column the insertion point is in, try:

Code:

Sub Count_Column_Text()
Dim TargetText As String, StrMsg As String
Dim Col As Long, i As Long, j As Long, Rng As Range
With Selection
  If .Information(wdWithInTable) = False Then
    MsgBox "The cursor must be within a table cell.", , "Cursor outside table"
    Exit Sub
  Else
    Col = .Cells(1).ColumnIndex
    TargetText = InputBox("Enter text to search. " & _
      "This macro will search the column you have " & _
      "selected for this text and count how many " & _
      "times it appears in that column. Do you want to continue?", _
      "Enter text to find and count")
    With .Tables(1)
      For i = 1 To .Rows.Count
        Set Rng = .Cell(i, Col).Range
        With .Cell(i, Col).Range
          With .Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = TargetText
            .Forward = True
            .Wrap = wdFindStop
            .Format = False
            .MatchCase = False
            .MatchWholeWord = True
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
            .Execute
          End With
          Do While .Find.Found
            If .Duplicate.InRange(Rng) Then
              j = j + 1
              .Collapse wdCollapseEnd
              .Find.Execute
            Else
              Exit Do
            End If
          Loop
        End With
      Next
    End With
    Select Case j
      Case 0: StrMsg = "The text " & TargetText & " was not found in the selected column "
      Case 1: StrMsg = "There is 1 occurrence of the text " & TargetText & " in the selected column "
      Case Else: StrMsg = "There are " & j & " occurrences of the text '" & TargetText & "' in the selected column "
    End Select
    MsgBox StrMsg & Col & ".", , "Text search in column"
  End If
End With
End Sub

Marrick13 · #6 11-16-2014, 09:42 PM

Thanks - that's better, although there is a reason I included the 'On Error GoTo' code. That's because for many VBA functions to work on a table, you need only place the cursor to the immediate right of a table and not in it. When it's to the right, VBA can count the table's columns and rows, for example, just as it can when it's actually within the table. VBA also considers 'If .Information(wdWithInTable)' to be true when the cursor is to the immediate right of the table.

Your line ' Col = .Cells(1).ColumnIndex" tells the code which column to search, but if the cursor is to the right of the table, 'If .Information(wdWithInTable)' will be true but the code will error because there is no column index. That's why I had the 'On Error GoTo NotATable' statements (see attached). It's the only way I could find to tell the macro that the pointer was actually inside the table.

Now it works the way I want it except that it still doesn't perform a MATCHWHOLEWORD search, at least not to my liking. My test table has 21 cells containing 'TO BE SCANNED' and 30 containing "scanned' (all in the same column). Searching on 'TO BE SCANNED' produces 21 hits, as it should, but searching on 'scanned' produces 51, which means it is counting both the cells containing 'TO BE SCANNED' as well as those containing 'scanned'. What I want is to get 30 results when I search on 'scanned'. Isn't that what a MATCHWHOLEWORD search does? That property is true in the code but doesn't seem to work as I expected it to, so is there another way?

macropod · #7 11-16-2014, 10:48 PM

Quote:

Originally Posted by Marrick13

Now it works the way I want it except that it still doesn't perform a MATCHWHOLEWORD search, at least not to my liking. My test table has 21 cells containing 'TO BE SCANNED' and 30 containing "scanned' (all in the same column). Searching on 'TO BE SCANNED' produces 21 hits, as it should, but searching on 'scanned' produces 51, which means it is counting both the cells containing 'TO BE SCANNED' as well as those containing 'scanned'. What I want is to get 30 results when I search on 'scanned'. Isn't that what a MATCHWHOLEWORD search does?

Unless your text consists of 'TO BE SCANNED' in uppercase and 'scanned' in lowercase, there won't be a ready way of excluding the 'TO BE SCANNED' text from the 'scanned' results, since 'scanned' and 'SCANNED' are both whole words. If 'TO BE SCANNED' is in uppercase and 'scanned' is in lowercase, all you need do is set:
.MatchCase = True

Marrick13 · #8 11-17-2014, 04:33 AM

As I suspected - thanks for all of your help!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro help - search for value, paste a value in another column	IRollman	Excel Programming	1	01-14-2014 01:05 PM
Excel Fomula to search for a string and display value from different column	zeeshanbutt	Excel	1	07-29-2012 12:48 AM
Word 2007: Automatically left-justified centered table or column?	wornways	Word	1	03-13-2012 12:18 AM
Need to search a column for a macth and return a result from a third column	pdfaust	Excel	2	02-03-2011 03:02 PM
How do I reference a merged cell in a multi column & row table in MS Word ('03')?	jihanemo	Word Tables	0	03-18-2009 08:33 AM