Smarter Macro for Multiple Find/Replace's?

[axelgeorge]

Hi,

I’m George, fairly new to Excel/Marco programming. I have (what I hope) is a rudimentary question.

I found this thread describing a way to do multiple Find/Replace’s in a large chunk of data:
http://www.mrexcel.com/forum/showthread.php?t=486818
I like this solution, but I wonder if there is a way to re-write this Macro to be more accurate.

To explain: The above thread describes a Macro which has two columns defined as “myRange.” The data in these columns can effectively be thought of as Find/Replace search pairs. The data-to-be-searched is defined as “myList.” The operation of the Macro works as follows:

For each search pair in myRange:
      Search through all of myList, make substitutions when found.

I tried this and it worked great. However, the above algorithm revisits every item in myList multiple times, potentially overwriting data in subsequent visits. I’d prefer a solution where each item in myList is visited once, but on that one visit we run through all the Find/Replace options at that time. (We can also assume that all of myList is in one column.) It occurs to me that if I rewrite the Macro’s loop, I could do it:

For each item in myList:
      Search item for every “Find” item in myRange
          If found; replace with corresponding “Replace” item in myRange

Here’s my clumsy attempt:

Sub multiFindNReplace()
      Dim myList, myRange
      Dim ListIterator As Integer
      Dim RangeIterator As Integer
      Set myList = Sheets("sheet3").Range("A8:B10") 'two column range where find/replace pairs are
      Set myRange = Sheets("sheet3").Range("D1:D100") 'range to be searched
      For Each cel In myRange.Columns(1).Cells
          For Each cel In myList.Columns(1).Cells
              if myRange(RangeIterator) >contains< myList(1)(ListIterator)
                myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value '???
      Next cel
  End Sub

Obviously this would make zero sense to Excel, but I can’t see how to fix it. Any advice?

Thanks!
-G

 

[BenMiller]

I don't know anything about VBA but try this http://www.mrexcel.com/forum/showpost.php?p=3025921&postcount=3