Results 1 to 10 of 10

multiple find and replace macro

This is a discussion on multiple find and replace macro within the General Excel Discussion & Other Questions forums, part of the Question Forums category; ok, i've got to do about 500 different find and replace operations so getting this macro written could save me ...

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Location
    Maryland, USA
    Posts
    1,173

    Default multiple find and replace macro

    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

  2. #2
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,170

    Default Re: multiple find and replace macro

    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 by Weaver; Aug 7th, 2010 at 01:56 PM.
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Location
    Maryland, USA
    Posts
    1,173

    Default Re: multiple find and replace macro

    in the above example i had sheet with

    mit der
    mit den
    mit dem

    and it only changed one

  4. #4
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,170

    Default Re: multiple find and replace macro

    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
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  5. #5
    Board Regular
    Join Date
    Mar 2010
    Location
    Maryland, USA
    Posts
    1,173

    Default Re: multiple find and replace macro

    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

  6. #6
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,170

    Default Re: multiple find and replace macro

    Shame on me for not reading your post fully!
    Quote Originally Posted by kylefoley76 View Post
    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
    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.
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  7. #7
    Board Regular
    Join Date
    Mar 2010
    Location
    Maryland, USA
    Posts
    1,173

    Default Re: multiple find and replace macro

    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

  8. #8
    Board Regular
    Join Date
    Mar 2010
    Location
    Maryland, USA
    Posts
    1,173

    Default Re: multiple find and replace macro

    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

  9. #9
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,170

    Default Re: multiple find and replace macro

    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
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    24,685

    Default Re: multiple find and replace macro

    Quote Originally Posted by kylefoley76 View Post
    ... i need it written for word not excel. i know this is an excel forum, but all the word forums have hardly any visitors
    Never-the-less a question about Word does not belong in the Excel Questions forum. Therefore I have moved it.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com