Solved

How to find if a word/phrase NOT FOUND in a table

Posted on 2007-03-29
6
484 Views
Last Modified: 2013-11-25
Hi experts!

This is part of a macro that I wrote which searches through a number of Word tables for a company/brand and if it finds it IN THE FIRST COLUMN to copy that row to another Word document. It works great as long as all the companies/brands are in every table but if one is missing, it just jumps to the next table. This is bad since I then go back and copy the title of the table and paste it into the first row and so if the company/phrase isn't found, the labels no longer match the data. I need a way so that if it doesn't find that company/brand in that table, IN THE FIRST COLUMN, it will jump to the other Word document and put in the phrase "COMPANY NOT FOUND!" Thanks in advance. You guys are amazing!

------------------------------------------------------------------

dim x as integer
dim WhatColumn as integer
dim InTable as integer

for x=1 to NumberOfTables

    Selection.Find.ClearFormatting

    With Selection.Find
        .Text = "XYZ Company"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = True
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With

    Selection.Find.Execute

' ---------------------------------------------------------------------------------------------------
' Code to see what column the macro is in and if it is not in the first column of a table, ignore it
' ---------------------------------------------------------------------------------------------------

    WhatColumn = Selection.Information(wdStartOfRangeColumnNumber)
    InTable = Selection.Information(wdWithInTable)

    If WhatColumn > 1 Or InTable = False Then
        x = x - 1
        GoTo TheNextOne1
    End If

    Selection.SelectRow
    Selection.Copy

' Jump to other Word Document and paste the row in. If the company/phrase not found in that table, create a blank row and paste "COMPANY NOT FOUND!" in first column of this blank row.

    Switch_Windows
    Selection.Paste
    Switch_Windows
    Selection.Find.Execute

TheNextOne1:
next x

'----------------------------------------------------------

Sub Switch_Windows()

z = ActiveWindow.Index

If z = 1 Then
    ActiveWindow.Next.Activate
    z = ActiveWindow.Index
Else
    ActiveWindow.Previous.Activate
    z = ActiveWindow.Index
End If

End Sub
0
Comment
Question by:Eddie_Aeffect
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 17

Expert Comment

by:ramrom
ID: 18840986
Following assumes you are looking for exactly one company, as you do in your code above
                        
for x=1 to NumberOfTables
    activedocument.tables(x).Columns(1).Select
    Selection.Find.ClearFormatting

    With Selection.Find
        .Text = "XYZ Company"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = True
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With

    if selection.find.execute then
      ' process the item
    else
      ' process absense of item
    end if
  end with
next
0
 

Author Comment

by:Eddie_Aeffect
ID: 18844356
Hi ramrom!

I ran your code and it tells me ERROR 5992 - CANNOT ACCESS INDIVIDUAL COLUMNS IN THIS COLLECTION BECAUSE THE TABLE HAS MIXED CELL WIDTHS. I do have merged cells where I label columns (i.e., GENDER for Male and Female). The frustrating thing is that the cells in the first column are never merged.
0
 
LVL 17

Accepted Solution

by:
ramrom earned 400 total points
ID: 18846150
Let's not use find. There does not seem to be a way to constrain it to the selection! So try this:

Sub test()
NumberOfTables = 2 ' I added this for my test case
For x = 1 To NumberOfTables
    Found = False
    With ActiveDocument.Tables(x)
        For r = 2 To .Rows.Count ' skip header row
            If InStr(.Rows(r).Cells(1).Range.Text, "XYZ Company") Then
              ' process the item
              Found = True
              Exit For
            End If
        Next
        If Not Found Then
            ' process absense of item
        End If
    End With
Next
End Sub
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:Eddie_Aeffect
ID: 18853743
Hi ramrom!

Don't know how long I can keep this open before it becomes rude so I think I'll close it now. Your answer didn't quite work because it was looping through the tables without actually landing in the tables so I am using:

For x = 1 To MaximumTables
     Selection.GoTo What:=wdGoToTable, Which:=wdGoToFirst, Count:=x, Name:=""
 . . .
 . . .
next x

And playing around with the code from there. I did learn some stuff from your code examples and I thank you for that!

Eddie




0
 
LVL 17

Expert Comment

by:ramrom
ID: 18855466
I'm not sure why you say "without actually landing in the tables".

ActiveDocument.Tables(x).Rows(r).Cells(1).Range.Text refers to the text of column 1 of row r of table x.

If instr() returns true then ActiveDocument.Tables(x).Rows(r) gives you the entire row.

What more did youwant?
0
 

Author Comment

by:Eddie_Aeffect
ID: 18858725
I ran the InTable function and it was telling me that it was not in a table. My fault. I actually ended up rewriting my code to store the values into an array and then creating a table in my second document and filling it. No need to keep switching between documents if I don't have to. thanks for all the help!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question