Ed Matsuoka
asked on
How to find if a word/phrase NOT FOUND in a table
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.ClearFormat ting
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(wdSt artOfRange ColumnNumb er)
InTable = Selection.Information(wdWi thInTable)
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.Acti vate
z = ActiveWindow.Index
End If
End Sub
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.ClearFormat
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(wdSt
InTable = Selection.Information(wdWi
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.Acti
z = ActiveWindow.Index
End If
End Sub
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
I'm not sure why you say "without actually landing in the tables".
ActiveDocument.Tables(x).R ows(r).Cel ls(1).Rang e.Text refers to the text of column 1 of row r of table x.
If instr() returns true then ActiveDocument.Tables(x).R ows(r) gives you the entire row.
What more did youwant?
ActiveDocument.Tables(x).R
If instr() returns true then ActiveDocument.Tables(x).R
What more did youwant?
ASKER
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!
for x=1 to NumberOfTables
activedocument.tables(x).C
Selection.Find.ClearFormat
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