Advanced find and replace?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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????)
 
Thanks Dave! I am new to macros but will check out the web reference you suggested
Cheers
Dan
 
Back
Top