Solved: Find Replace Help

[Frida]

Hi,

I'm trying to write a macro to replace a value from a table with another from the same table throughout the entire document. I have been able to pull the values from the table and loop through them, but I cannot get the find/replace portion to work- the macro runs, but no changes are made.

The text to be replaced is in different formats, some in tables etc. I am OK with Excel, but this is the first time I've tried to do something in Word.

Below is what I have.... any thoughts would be gladly recieved!


For MyRow = 2 To rowCt
RawWhat = myTable.Cell(MyRow, 1).Range.Text
RawWith = myTable.Cell(MyRow, 2).Range.Text

TrimWhat = Left(RawWhat, Len(RawWhat) - 1)
TrimWith = Left(RawWith, Len(RawWith) - 1)

Set wdRng = ActiveDocument.Content
With wdRng.Find
.Text = TrimWhat
.Replacement.Text = TrimWith
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
wdRng.Find.Execute Replace:=wdReplaceAll

Next MyRow

[fumei]

Ready?

1. The reason it does not work for you is this:

TrimWhat = Left(RawWhat, Len(RawWhat) - 1)
It is the 1. if you change it to 2, it works fine. The end-of-cell marker is a TWO character string. Not one.

2. It MUCH better to have a permanent Function that does this. That way you can simply call the function anytime you want to work with text from table cells.

Function CellText(strIn As String) As String
CellText = Left(strIn, Len(strIn) - 2)
End Function
Thus, it would be in your code:

TrimWhat = CellText(myTable.Cell(MyRow, 1).Range.Text)


3. For the most part it is better if you post full code. To duplicate this I has to declare variables myself, rather than just being able to copy your code.

4. It would also be MUCH better if you did not use variables that you do not actually need. Here is code that works as you want.

Option Explicit
Function CellText(strIn As String) As String
CellText = Left(strIn, Len(strIn) - 2)
End Function

Sub YaddaBlah()
Dim MyRow As Long
Dim myTable As Table
Dim wdRng As Range

Set myTable = ActiveDocument.Tables(1)

For MyRow = 2 To myTable.Rows.Count
Set wdRng = ActiveDocument.Content
With wdRng.Find
.Text = CellText(myTable.Cell(MyRow, 1).Range.Text)
.Replacement.Text = CellText(myTable.Cell(MyRow, 2).Range.Text)
.Forward = True
.Wrap = wdFindContinue
End With
wdRng.Find.Execute Replace:=wdReplaceAll
Next MyRow
End Sub
Notice the variables are declared. Also notice that There is no RawWhat - it is not needed. There is no TrimWhat - it is not needed. Thre is no rowCt - it is not needed (unless there is something else going on where you DO need it, the point being you do not need it here.).

[Frida]

Hi Fumei!

Many thanks for the quick response. I had no idea that the end-cell marker was a 2 character deal, so that explains a lot :).

I had intended to clean up a bit when it was working by removing the raw variables, but I hadn't though of using a UDF to trim the text.

Much appreciated help! I definitely wouldn't have figured that out myself.

Frida

[fumei]

I think that UDF is the most often used of all of my UDF. If you use objects more (and this is a good idea), you can also have a slightly different version. A cell version.

Option Explicit
Function CellText2(oCell As Cell) As String
CellText = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
End Function
That way you can can pass a cell object, For example:

Sub CollateCellText()
Dim oCell As Cell
Dim msg As String
For Each oCell In ActiveDocument.Tables(1).Range.Cells
msg = msg & "Cell " & oCell.RowIndex & "," & oCell.ColumnIndex & _
" text is: " & CellText2(oCell) & vbCrLf
Next
Selection.TypeText msg
End Sub
This could result in something like:

Cell 1,1 text is: This cell 1
Cell 1,2 text is: That cell 2
Cell 1,3 text is: This cell 3
Cell 2,1 text is: That cell 4
Cell 2,2 text is: That cell 5
Cell 2,3 text is: That cell six
Cell 3,1 text is: That cell seven
Cell 3,2 text is: That cell eight
Cell 3,3 text is: That CELL nine

In other words, gathering up all the text from every cell in the given table.

[Frosty]

Just a quick brainstorm... this is a pretty neat function concept. You're essentially using a microsoft table as an "array" of find/replace commands.

In addition to Gerry's comments, I might add the following conceptual approaches.

1. Add a third column to your table, and mark it (with an "X" or something) when that particular Replace All has been performed.

2. Rather than use ActiveDocument.Content (which would include the table you're getting your search values from, thus changing your source data, and making it trickier to remember what was done), define your search range as everything but the table (not knowing where your table is, you may need 1 range or two).

3. In addition, just know that ActiveDocument.Content doesn't (I believe) check all of the various stories in Word... so I don't think you'll change any information in your headers and footers, any text boxes, etc. That may not be necessary for purposes of this function, but just wanted to point out that ActiveDocument.Content is a little misleadingly named.

I could see this being a broadly useful function in certain cases. If the table were in a separate document, if you needed to run multiple find/replace routines on a lot of different documents, it wouldn't be tough to separate it into two functions: a "GetMyFindReplaceInfo" and a "DoMyFindReplace" ... where one grabbed the table data, put it into a public array variable... and the other function looped through that public array variable in whatever activedocument you decided on.

[Frida]

Hi Gerry,

This is interesting to me, since as stated I have no experience using VBA in Word. The last code you posted there is really similar to something I use in Excel to identify records outside given specifications. I will definitely be doing some more Word based projects to get some more practice!

Frosty, your post touches on my next steps, to try and figure out how to identify different ranges within a Word document (outside ActiveDocument.Content). Maybe Gerry could recommend some light reading for me :)?

Cheers,

Frida

[fumei]

Frosty: "I could see this being a broadly useful function in certain cases. If the table were in a separate document, if you needed to run multiple find/replace routines on a lot of different documents, it wouldn't be tough to separate it into two functions:"

I do something like this, but I also use INI file items, as well than Word doc tables.

As for using ranges, learning how to use Ranges in Word well makes a HUGE HUGE HUGE difference.

"Identify" ranges? Hmmmmmmmm. Ummmm. Sort of, but technically I would would say generally you define the ranges you work with, although I can see "identify". In that I suppose you are identifying a range if you point to it.

If you clearly define your terms and requirements, I am pretty sure someone here will be able to help identify your ranges.

Hint: if you are not using bookmarks as a tool you may want to think about it. Using bookmarks is a very powerful tool in Word because that is EXACTLY what they are: identified/defined ranges.

[Frosty]

To supplement Gerry's point about bookmarks (which are a great concept to investigate, if you don't know about them-- they are similar in concept to named ranges in Excel)... but there is a difference between the concepts of "ranges" and "stories" in Word architecture.

Ranges are defined simply by a starting number and an ending number.

If you had a blank document, with the words "Frosty is Great" in the document, the following statements would all be true:
1. ActiveDocument.Content.End = 16
2. ActiveDocument.StoryRanges(wdMainTextStory).End = 16
3. ActiveDocument.Sections(1).Range.End = 16

However, the following would not:
4. ActiveDocument.Section(1).Headers(wdHeaderFooterPrimary).Range.End

You instinctively know why... because Frosty Is Great simply exists in the body of the document... not in the Header.

But look at #2 again-- that whole StoryRanges thing.
Word has a bunch of different stories as "areas" where text can be. It also has a couple of other things which act like stories, but aren't-- the graphic layers for each of the stories (I *think* they all have a graphic layer, but I'm not 100% sure... I do know that Word will get confused at times with the graphic layer). But this isn't about explaining bugs... just concepts.

So you can iterate through all of your story ranges with the find, or you can iterate through the likely ones when you're doing find/replace in code.

But even a recorded macro doesn't necessarily operate the same way when you run the macro as it does when you perform the action as a regular user.

Now put "Frosty Is Great" in your header as well.

Record a macro where you replace all instances of "Great" with "Fantastic"... after clicking through the "two changes" box, you'll end up with something like this:

Sub Macro1()
'
' Macro1 Macro
'
'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "Great"
.Replacement.Text = "Fantastic"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

But if you go back into the document, hit undo... and then run Macro 1 from the VBA interface, only the one in the "Main Story" will be changed, while the Header will be left alone.

That's why typically, if you're very concerned about changing all instances of something... you need to encapsulate your "Find All" functionality by iterating through each story element (which is a range) using something along the lines of:

Sub ReplaceAllSomething
Dim oStory as Range

For Each oStory in ActiveDocument.StoryRanges
ReplaceStuffInThisRange oStory.Range
Next
End Sub
Sub ReplaceStuffInThisRange (rngWhere)
With rngWhere.Find
'find/replace criteria here
End With
End Sub


That info, in addition to the bookmarks (and looking up hidden bookmarks as well) will send you on your way with ranges and the various stories.

Hope that helps.

[gmaxey]

Frosty,

In Word it can be a little tricky to find and replace "every" instance. You have to go a little deeper than just iterating each story range. There are the linked stories (e.g., headers and footers, textboxes) that need to be iterated. And rarely applicable, there are the shape ranges that may be present in header and footer story ranges. See:

http://gregmaxey.mvps.org/VBA_Find_And_Replace.htm

[Frosty]

Great info. Forgot about that stuff.

Unfair when the experts have already clearly disseminated the info, and I'm making it up as I go!

But the lesson is the same: don't necessarily trust that Word accurately records what it is doing. ;)

[Frida]

I have a small follow up query, if anyone can help......

My "found" text is all lower case, but often my "replacement" text contains sub script characters. When it is replaced, the subscript characters revert to clear formatting. How do I deal with this issue?

[Frosty]

subscript is font formatting, and I don't believe there are any special find codes to identify it within the search (or replacement) string.

So you can't specifically search for "My Name Is Bob" although you can search for all Bold or all Not Bold versions of that sentence. Of course, you'll still find "My Name Is Bob" ... but how to replace is trickier.

My guess is you'll need to write a subroutine which identifies whether there is any special (and necessary) font formatting within your replace string. And if that's the case, you will have to branch into a loop which finds each instance, and then replaces the range "manually" (by copying/pasting, or setting the .FormattedText property of the found range to range you got your replacement text from.

That may mean a slight restructure of the way you're getting the text out of the cell, since you're returning a string (which isn't the formatting), and you may need the range.

[Frida]

Hi Frosty,

I'm not so worried about the search text, only the format of the replacement text. For example, one of the replacements I need to make is from "unit1" to m2 (with the 2 in super-script). Currently, the format of the replacement text is cleared.

Frida

[Frosty]

Right, but it's the same problem. And you keep calling it the formatting of the replacement text. It's not the replacement text... it's only PART of the replacement text.

You can't format only part of your replacement text... it's all or nothing.

If you want to search for "unit1" and replace with "m2" where "2" is some other formatting than "m" (in your case, superscript formatting), I don't believe you will be able to do it with a replace all function.

Any format searching/replacing applies to ALL of the text in the search/replace string.

However, you can find your text "unit1" and manipulate the found range object. But I think this is going to require a revamp of your code, since in a cursory view it looks like you're always grabbing .text (which doesn't include formatting information).

I can only give you general pointers until you have a specific code question. But I think you're looking for a magical switch which will work with all of your code, and there isn't one. Your essential contruct (replacing all instances of Cell1.Text with Cell2.text) is flawed, if you are adding partial font formatting into the mix.

[Frosty]

Maybe I'm being too obtuse... but I think you mentioned you're familiar with vba programming, but not necessarily the word object model?

If you are not familiar with the programming concepts of Do loops, or working with range objects... why don't you post a simplified version of your project (or the couple of routines you currently have), and you may be able to get an answer which is a little more concrete and less conceptual.

[fumei]

If you have an AutoText of m2 (with the 2 as superscript), then the following replaces "unit1" with THAT AutoText (m2, with the 2 as superscript). The AutoText of m2 is named "super".

Sub ToSuper()
Dim r As Range
Set r = ActiveDocument.Range
With r.Find
Do While .Execute(FindText:="unit1", Forward:=True) = True
with r
.Text = "super"
.InsertAutoText
.Collapse 0
Loop
End With
End Sub

[Frosty]

I think Gerry is missing an End With in that code... but that could work. You don't necessarily need to to use .Text = "super" followed by .InsertAutotext as you can do the following (assuming you define the template your autotext entry exists in):

oMyAutoTextTemplate.AutoTextEntries("super").Insert where:myRange, RichText:=True

But I think this goes back to working from the text within a table... so the overhead of creating the autotext entry from the Replace column of cells may not be worth just grabbing whatever the replace .range is, and setting the found range.formattedtext property to the replace .range.formatted text property.

I set up a simple document with a 2 column table at the top, which would replace: Quick with Fast, Brown with Sepia, and Fox with Fox the 3rd (with the rd superscripted)

And then run the following code. This obviously isn't optimized for your scenario, but it will point you in the right direction (if you can use ReplaceAll, it would be better, especially on a longer document. So I think you would get bang for your coding buck to identify whether you could use Replace all or needed to use a loop.

Public Sub UsingATableForFindReplace()
Dim oSearchTable As Table
Dim rngFindWhat As Range
Dim rngReplaceWith As Range
Dim rngSearch As Range
Dim iRowCount As Integer

'get the table
Set oSearchTable = ActiveDocument.Tables(1)
'get the range of the document
Set rngSearch = ActiveDocument.Content

'modify the range, so we don't replace any values in our table
rngSearch.Start = oSearchTable.Range.End
With oSearchTable
For iRowCount = 1 To .Rows.Count
Set rngFindWhat = .Cell(iRowCount, 1).Range
rngFindWhat.MoveEnd wdCharacter, -1
Set rngReplaceWith = .Cell(iRowCount, 2).Range
rngReplaceWith.MoveEnd wdCharacter, -1

'now do our search
With rngSearch.Find
.Text = rngFindWhat.Text
.Forward = True
'so it stops at the end of the document
.Wrap = wdFindStop

Do While .Execute = True
rngSearch.FormattedText = rngReplaceWith.FormattedText
'collapse the range to the end of what we just searched, and search again
rngSearch.Collapse wdCollapseEnd
Loop
End With
Next
End With
End Sub

[Frosty]

Well, just because I was curious... here is an example of what I mean in branching the logic based on whether the replacement text has some special formatting. You could obviously make the fRangeHasSuperScript function much more robust to include any formatting in the replacement cell which you'd like to identify and retain.


Public Sub UsingATableForFindReplace()
Dim oSearchTable As Table
Dim rngFindWhat As Range
Dim rngReplaceWith As Range
Dim rngSearch As Range
Dim iRowCount As Integer

'get the table
Set oSearchTable = ActiveDocument.Tables(1)
'get the range of the document
Set rngSearch = ActiveDocument.Content

'modify the range, so we don't replace any values in our table
rngSearch.Start = oSearchTable.Range.End
With oSearchTable
For iRowCount = 1 To .Rows.Count
Set rngFindWhat = .Cell(iRowCount, 1).Range
rngFindWhat.MoveEnd wdCharacter, -1
Set rngReplaceWith = .Cell(iRowCount, 2).Range
rngReplaceWith.MoveEnd wdCharacter, -1

'now do our search
With rngSearch.Find
.Text = rngFindWhat.Text
.Forward = True
.Replacement.Text = rngReplaceWith.Text
'so it stops at the end of the document
.Wrap = wdFindStop

If fRangeHasSuperScript(rngReplaceWith) Then
Do While .Execute = True
rngSearch.FormattedText = rngReplaceWith.FormattedText
'collapse the range to the end of what we just searched, and search again
rngSearch.Collapse wdCollapseEnd
Loop
Else
.Execute Replace:=wdReplaceAll
End If
End With

Next
End With

End Sub
Public Function fRangeHasSuperScript(rngWhich As Range) As Boolean
Dim rngChar As Range

For Each rngChar In rngWhich.Characters
If rngChar.Font.Superscript = True Then
fRangeHasSuperScript = True
Exit For
End If
Next
End Function

[fumei]

Yup, missing an End.

Plus I totally forgot the original thing about the replacement text was in another cell of the table. Doh. That is why I suggested an AutoText.

You don't necessarily need to to use .Text = "super" followed by .InsertAutotext as you can do the following (assuming you define the template your autotext entry exists in):

oMyAutoTextTemplate.AutoTextEntries("super").Insert where:myRange, RichText:=True
True you can do it that way, and that is the way I used to do it, but I find it faster to type in the name as .Text and .InsertAutoText.

.Text = "super"
.InsertAutoText

versus

oMyAutoTextTemplate.AutoTextEntries("super").Insert where:myRange, RichText:=True
The Where is already defined (as you are using the Range), and the template does not need to be defined because as long as it is available, it will work. But technically, yup, doing it as a AutoTextEntry.Insert is equivalent.

[Frosty]

Actually, slight flaw in my code... try this instead.

Public Sub UsingATableForFindReplace()
Dim oSearchTable As Table
Dim rngFindWhat As Range
Dim rngReplaceWith As Range
Dim rngSearch As Range
Dim rngOriginalSearch As Range
Dim iRowCount As Integer

'get the table
Set oSearchTable = ActiveDocument.Tables(1)
'get the range of the document
Set rngSearch = ActiveDocument.Content

'modify the range, so we don't replace any values in our table
rngSearch.Start = oSearchTable.Range.End
'retain a copy of our original search range

Set rngOriginalSearch = rngSearch.Duplicate
With oSearchTable
For iRowCount = 1 To .Rows.Count
Set rngFindWhat = .Cell(iRowCount, 1).Range
rngFindWhat.MoveEnd wdCharacter, -1
Set rngReplaceWith = .Cell(iRowCount, 2).Range
rngReplaceWith.MoveEnd wdCharacter, -1

'now do our search
With rngSearch.Find
.Text = rngFindWhat.Text
.Forward = True
.Replacement.Text = rngReplaceWith.Text
'so it stops at the end of the document
.Wrap = wdFindStop
'see if we've got special formatting to deal with
If fRangeHasSuperScript(rngReplaceWith) Then
Application.UndoRecord.StartCustomRecord "Replaced: " & rngReplaceWith.Text
Do While .Execute(Replace:=wdReplaceOne) = True
rngSearch.FormattedText = rngReplaceWith.FormattedText
'collapse the range to the end of what we just searched, and search again
rngSearch.Collapse wdCollapseEnd
Loop
Application.UndoRecord.EndCustomRecord
'reset our range
Set rngSearch = rngOriginalSearch.Duplicate
Else
.Execute Replace:=wdReplaceAll
End If
End With
Next
End With
End Sub
Public Function fRangeHasSuperScript(rngWhich As Range) As Boolean
Dim rngChar As Range

For Each rngChar In rngWhich.Characters
If rngChar.Font.Superscript = True Then
fRangeHasSuperScript = True
Exit For
End If
Next
End Function

[Frosty]

Oh, and Gerry-- totally agree. Your way is much easier. I think I've indoctrinated myself to creating the minimal amount of undo actions. So I'm starting to play around with this new Application.UndoRecord thing they've exposed. It seems kind of buggy.

[fumei]

What the heck is UndoRecord?????

[Frosty]

A new feature to play around with and get frustrated at.

I put it into the code above. Basically, it allows you to "stamp" your coded sequence of actions. So the above code will either show up as:

VBA-Find.Execute2007
Replaced: Fox the 3rd
VBA-Find.Execute2007

OR

a whole bunch of
VBA-Find.Execute2007
VBA-Range.FormattedText

It essentially is a microsoft provided version of setting the bookmark in the undo list.. which I always found more trouble than it was worth.

[Frosty]

Changed the name of one of the above functions, and gave an example of how to add additional formatting to the criteria. Also put in a warning note at the top of the routine. There are many ways to do this, and there may be a faster way too

'-----------------------------------------------------------------------------------------------
' Check if the passed range has any formatting we want to preserve
' NOTE: it goes through each character in the passed range.
' Will be very slow on larger ranges
'-----------------------------------------------------------------------------------------------
Public Function fRangeHasSpecialFormatting(rngWhich As Range) As Boolean
Dim rngChar As Range
Dim bPreserveThisFormatting As Boolean

'check each character in the passed range
For Each rngChar In rngWhich.Characters
'check our font formatting
With rngChar.Font
If .Superscript = True Then
bPreserveThisFormatting = True
ElseIf .Subscript = True Then
bPreserveThisFormatting = True
End If
End With

'exit our for if any of our above criteria was met
If bPreserveThisFormatting Then
fRangeHasSpecialFormatting = True
Exit For
End If
Next
End Function