find and replace using a table

[samuelimtech]

Hi all,

this should be quite a quick answer, I have a table at the bottom of the document with a list of numbers in column 1 and a list of numbers in column 2, the numbers in column 1 all appear in the document, I want to perform a find and replace function to all of the numberss, essentially find all of the Numbers in the text that are the same as column 1 and replace them with the equivelent in column 2.

thanks for any help

[macropod]

Do any of the numbers in column 1 also appear in column 2?

[samuelimtech]

that is possible, my plan is to just loops through the table then use a find an replace but for some reason its not working and i cant suss out why. the numbers that are in collumn one are for example M:1234, in the document it appears as M:1234 Chr(9), obviously followed by a tab not actually Chr(9). the find and replace without specifying the tab works if there isnt a tab in the document but there is and i cant change that.


Sub FindandReplace()
Dim RNG As Range
Dim Tbl As Table
Dim j As Integer
Dim i As String
Dim k As String
Set Tbl = ActiveDocument.Tables(1)
With Tbl
For j = 2 To ActiveDocument.Tables(1).Rows.Count
i = "Boo" & Chr(9)
'i = Left(Tbl.Cell(j, 1), Len(Tbl.Cell(j, 1).Range) - 1) & Chr(9)
k = Left(Tbl.Cell(j, 2), Len(Tbl.Cell(j, 2).Range) - 1)
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = i
.Replacement.Text = k ' not sure if this will be neccessarey in the real doc
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Next
'
End With
End Sub

[macropod]

Without knowing exactly what's in the table, it's difficult to advise. Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

The reason I originally asked about whether the same numbers might appear in both sides is that, unless you're careful, you could end up with both lots ending up the same.

FWIW, the basic code I'd use would be something like:

Sub Demo()
Application.ScreenUpdating = False
Dim RngDoc As Range, oTbl As Table, i As Long
Dim RngFnd As Range, RngRep As Range
With ActiveDocument
Set RngDoc = .Range
Set oTbl = .Tables(.Tables.Count)
RngDoc.End = oTbl.Range.Start
With RngDoc.Find
.ClearFormatting
.Replacement.ClearFormatting
.Format = False
.MatchWildcards = True
For i = 2 To oTbl.Rows.Count
Set RngFnd = oTbl.Cell(i, 1).Range
RngFnd.End = RngFnd.End - 1
Set RngRep = oTbl.Cell(i, 2).Range
RngRep.End = RngRep.End - 1
.Text = RngFnd.Text
.Replacement.Text = RngRep.Text
.Execute Replace:=wdReplaceAll
Next
End With
End With
Set RngFnd = Nothing: Set RngRep = Nothing
Application.ScreenUpdating = True
End Sub

[samuelimtech]

Thanks for your Help!

[macropod]

I suggest you try the code I posted...

[samuelimtech]

It doesnt seem to work

[macropod]

It works for me with the limited sample you posted.

[samuelimtech]

It does? im not sure whats happened some obvious mistake but whne i copied it in last time it didnt, thank you for your help.
If its not too much trouble can you explain why the code i used in the documnent didnt work?

thank you soo much

[macropod]

Your code doesn't work because the two '- 1' expressions need to be '- 2'. Table end-of-cell markers consist of two characters. My code works with '- 1' because it uses a range object instead of the text length.