Multiple Find and Replace with VBA

  • I am trying to create a VBA script that I can run anytime the task needs done. I need it to find one value within a specific range, and replace it with a forumla.


    I thought I'd gotten it to work, but it appears it will only run the first time I execute the macro, even if the next time I execute it more values exist that should be replaced.


    Can you help?


    Here is what I have so far. (hyperlink URL has been changed):


    Code
    1. Sub ReplaceX()
    2. Range("P5:AC39").Replace What:="Y", Replacement:="=HYPERLINK(""http://www.example.com"",""X"")", LookAt:=xlPart, _
    3. SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    4. ReplaceFormat:=False
    5. End Sub
  • Re: Find and Replace with VBA. Run Multiple Times


    Hi,

    I use this code to replace special characters in text and it replaces all of them. Maybe it can give you a clue to your problem. Maybe the "With" statement is the key. Hope this helps.

  • Re: Find and Replace with VBA. Run Multiple Times


    Hi stringy,


    Welcome to Ozgrid.


    Appreciate your help. Please go through the forum rules and ensure that you have understood the rules that you have agreed to follow. All codes should be wrapped with code tags. Since this is your first post, I have added code tags for you.

  • Re: Find and Replace with VBA. Run Multiple Times


    Code
    1. Sub ReplaceY()
    2. Range("P5:AC39").Replace "Y", "=HYPERLINK(""http://www.example.com"",""X"")", xlformulas,xlPart
    3. End Sub