Matrix find/replace script?

[miwoodar]

Greetings all!

I'm trying to write a find/replace script that will look at two columns of info in a single range on a single tab. If the script finds abc in the first column and the second column says xyz the script will find all occurances of abc in the entire workbook and replace them with xyz. It would then cycle to the next match. There are about 2000 total matches to be found and replaced. This is something I've always done manually in the past (not as often as I would like due to the effort involved). Doing it manually is also error prone. I don't get the opportunity to write code very often and writing a script like this is beyond my skill set. Any help?

____________________
Mike

 

[Bong]

For simplicity, let's say you have:
[tt]abc xyz
def hgi
jjj bbb[/tt]
in a1:b3
So you want go down the rows, set what's in column A to "look for" and what's in column B to "replace with".

set rngA=sheet1.[a1:b3]
for each rw in rngA.rows
  strLF=rw.cells(1)
  strRW=rw.cells(2)
  for each sh in thisworkbook.sheets
    set c = sh.Find(strLF, lookin:=xlValues)
    if not c is nothing then
      'here it depends on whether you're replacing the entire cell or searching the contents of the cell
    do
      set c=sh.findnext(c)
      'likewise here
    Loop While Not c Is Nothing 
    end if
  next
next

_________________
Bob Rashkin