Advanced find and replace?

[Guest]

Is there a way to Find and Replace a string by directing excel to a lookup table? I have a big spreadsheet with lots of text strings. I want to find all strings containing "abc", and replace it with "xyz". But I have about 40 such replacements to make, and was wondering if I could direct excel to a 2-column table, where it would see the 40 pairs of strings that I want it to find and replace. I am familiar with >Edit >Replace, but am hoping there is a way to automate and accelarate the task. Any ideas
Dan

 

[Dave Peterson]

With not too much error checking.

Option Explicit
Sub testme01()

Dim myCell As Range
Dim listWks As Worksheet
Dim changeWks As Worksheet

Set listWks = Worksheets("List")
Set changeWks = Worksheets("sheet1")

With listWks
For Each myCell In .Range("a1:a" _
& .Cells(.Rows.Count, "A").End(xlUp).Row).Cells
With changeWks.UsedRange
.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
Next myCell
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


I did assume that I could change just part of a cell (xlpart) and matching case
wasn't important.

You may want to check to make sure that both the "what" and "replacement" are
non-blank, too (or maybe you want that capability????)

 

[Guest]

Thanks Dave! I am new to macros but will check out the web reference you suggested
Cheers
Dan