VBA to search each row of a word table column

macropod · #1 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 · #2 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 · #3 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

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