VBA: working with a table
Thread poster: DZiW

DZiW
Ukraine
English to Russian
+ ...
May 9

1) I've got a long five-column table in a 300-page DOCX file.
2) In every row Column#1 contains a single word or a phrase.

3) How could one find this word/phrase in Column#4 of the same row, marking it bold?
4) The whole table should be processed line-by-line.

It's a one time job, so I need a solution preferably in VBA, yet I'm open to relevant tips and suggestions.


Thank you


 

wotswot  Identity Verified
France
Local time: 01:04
Member (2011)
French to English
VBA May 10

Do you mean mark contents of column 4 in bold ONLY IF it's identical to the contents of column 1?

 

DZiW
Ukraine
English to Russian
+ ...
TOPIC STARTER
Yes May 10

For example, the first line (row) contains:
#1 hello - #2 123654 - #3 qweasdzxc - #4 asd hello ghj - #5 asdzxc

How I could find the content of Column#1 ("hello") in Column#4, marking it bold?


 

wotswot  Identity Verified
France
Local time: 01:04
Member (2011)
French to English
VBA May 10

OK, so just to be totally accurate, only mark in bold the text in column 4 that matches the text in column 1.
Does the match have to be case-sensitive?


 

DZiW
Ukraine
English to Russian
+ ...
TOPIC STARTER
case/fragmentation May 10

I recheck several entries just to make sure and there seem no case issues.

However, it would be better have "whole words" only tacked, preventing possible fragmentation.


Meanwhile, a colleague mentioned that using MS Excel (instead of Word) might help, but I can't see a big difference with such an automation issue.


 

Rolf Keller
Germany
Local time: 01:04
English to German
Here you are May 11

This following should work, but might need some refinement:

Option Explicit

Sub ProcessOneTable()

Dim Cell_4_cell As Cell
Dim Cell_1_txt, Cell_4_txt As String
Dim ReplaceLoc, ReplaceLen As Integer
Dim Tabl As Table
Dim Rw As Row

' User has to select a Table upfront!
If Selection.Tables.Count < 1 Then Exit Sub
Set Tabl = Selection.Tables(1)
If Tabl.Columns.Count < 4 Then Exit Sub
If Tabl.Rows.Count
... See more
This following should work, but might need some refinement:

Option Explicit

Sub ProcessOneTable()

Dim Cell_4_cell As Cell
Dim Cell_1_txt, Cell_4_txt As String
Dim ReplaceLoc, ReplaceLen As Integer
Dim Tabl As Table
Dim Rw As Row

' User has to select a Table upfront!
If Selection.Tables.Count < 1 Then Exit Sub
Set Tabl = Selection.Tables(1)
If Tabl.Columns.Count < 4 Then Exit Sub
If Tabl.Rows.Count < 1 Then Exit Sub

For Each Rw In Tabl.Rows

Cell_1_txt = Rw.Cells(1).Range.Text
ReplaceLen = Len(Cell_1_txt) - 2 ' Don't count CR/LF
Cell_1_txt = Left(Cell_1_txt, ReplaceLen)
If Cell_1_txt = "" Then GoTo NextRow

Set Cell_4_cell = Rw.Cells(4)
Cell_4_txt = Cell_4_cell.Range.Text

ReplaceLoc = InStr(1, Cell_4_txt, Cell_1_txt, vbTextCompare) - 1
If ReplaceLoc < 0 Then GoTo NextRow

Cell_4_cell.Select

Selection.Collapse
Selection.MoveStart Unit:=wdCharacter, Count:=ReplaceLoc
Selection.MoveEnd Unit:=wdCharacter, Count:=ReplaceLen
Selection.Font.Bold = True

NextRow:
Next Rw

End Sub
Collapse


 

Joakim Braun  Identity Verified
Sweden
Local time: 01:04
German to Swedish
+ ...
With Excel May 11

Copy the table to Excel. Create a second target column next to the target column. Set its contents a formula returning the result of some string lookup method (SUBSTITUTE looks good) that looks for the source string (referencing the source column), and substitutes it adding some unique prefix and suffix.

Like so:
Source: "myword"
Target: "This is a text containing myword and other stuff"
Second target column using text substitution formula: "This is a text contai
... See more
Copy the table to Excel. Create a second target column next to the target column. Set its contents a formula returning the result of some string lookup method (SUBSTITUTE looks good) that looks for the source string (referencing the source column), and substitutes it adding some unique prefix and suffix.

Like so:
Source: "myword"
Target: "This is a text containing myword and other stuff"
Second target column using text substitution formula: "This is a text containing x_myword_x and other stuff"

Then cut/paste into a Word table and advanced-search-replace using wildcards, prefix/suffix, pattern substitution and formatting options.

Like so:
Find what: x_(*)_x
Replace with: \1 (the first matched pattern), setting font formatting option (at bottom of dialog) to Bold
(This will only work on the first occurrence of the string in the cell, no doubt that's fixable.)

Remove the extra column and you're done.


***

(Excel doesn't to rich text with formulas:
https://stackoverflow.com/questions/45443127/is-it-possible-to-formatting-bold-in-excel-formula)
Collapse


 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

VBA: working with a table

Advanced search






SDL MultiTerm 2019
Guarantee a unified, consistent and high-quality translation with terminology software by the industry leaders.

SDL MultiTerm 2019 allows translators to create one central location to store and manage multilingual terminology, and with SDL MultiTerm Extract 2019 you can automatically create term lists from your existing documentation to save time.

More info »
Anycount & Translation Office 3000
Translation Office 3000

Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search