multiple find and replace macro

K

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,553
ok, i've got to do about 500 different find and replace operations so getting this macro written could save me maybe 4 hours of work. i'm just trying to figure out how you change the macro so that you do more than one find and replace operation. i tried to change it myself but it didn't really work.

this is also for word by the way, but i figure that there cannot be much difference between word and excel visual basic. this macro works for one find and replace but not three.

Code: 
Sub Macro1()

    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = " mit der "
        .Replacement.Text = " mitder"
        .Text = " mit dem "
        .Replacement.Text = " mitdem"
        .Text = " mit den "
        .Replacement.Text = " mitden"
        .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
 
Sort by date Sort by votes
For this example, could you find "mit d" and replace with "mitd"?

Otherwise, you could create a table with the 'find' in column 1 and the 'replace' in column 2

It would be possible to loop through this table in vb and perform each find/replace in turn
 
Last edited:
Upvote 0
Something like

Code: 
Sub multiFindNReplace()
    Dim myList, myRange
    Set myList = Sheets("sheet3").Range("A8:B10") 'two column range where find/replace pairs are
    Set myRange = Sheets("sheet3").Range("D1:F100") 'range to be searched
    For Each cel In myList.Columns(1).Cells
        myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
    Next cel
End Sub
 
Upvote 0
weaver,

thanks for trying to help me with my problem but i need it written for word not excel. i know this is an excel forum, but all the word forums have hardly any visitors
 
Upvote 0
Shame on me for not reading your post fully!
kylefoley76 said:
weaver,

thanks for trying to help me with my problem but i need it written for word not excel. i know this is an excel forum, but all the word forums have hardly any visitors
Click to expand...
I can only suggest you paste all your text into Excel run the code and then paste it back. I can't imagine how you'd perform this in Word.
 
Upvote 0
this is a text of 2 million words and 6,000 pages. i think it would overload excel. but thanks for taking the time to try and help me
 
Upvote 0
ok, i got it to work, i just recopied it

Sub Macro1()
'

'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " mit der "
.Replacement.Text = " mitder"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " mit den "
.Replacement.Text = " mitden"
.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
 
Upvote 0
Just a thought. If you've got a lot of swaps to make, something like this might work.
This way you wouldn't have to copy out the code multiple times.

Code: 
findArray = array(" mit der "," mit den "," mit dem ")
replArray = array(" mitder"," mitden"," mitdem")

for i =0 to ubound(findArray)
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = findArray(i)
.Replacement.Text = replArray(i)
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
next i
Let me know if it works
 
Upvote 0
kylefoley76 said:
... i need it written for word not excel. i know this is an excel forum, but all the word forums have hardly any visitors
Click to expand...
Never-the-less a question about Word does not belong in the Excel Questions forum. Therefore I have moved it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA .Find WildCard not working
Replies
4
Views
494
Joe4
Joe4
D
  • Question Question
Bold specific text whilst ignoring other specific text...
Replies
0
Views
90
Dan10
D
P
  • Question Question
Copy an excel table and paste it into word
Replies
2
Views
1K
Paskie_EU
P
C
  • Solved
Help with Word VBA - Replacing text in TextBox
Replies
6
Views
4K
Macropod
Macropod
S
  • Question Question
VBA To Delete Hidden Text in Word
Replies
9
Views
5K
ZVI
ZVI

Forum statistics

Threads
1,226,797
Messages
6,193,051
Members
453,772
Latest member
aastupin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top