Is there a way to set a batch of find&replaces ??

S

skuba

I would like to set a macro, module or whatever automatizes a batch of
finds and replaces.
I have a bunch of find and replaces that I need to do weekly on a file.
How can I make one command that will take care of that?

Let's say one command that will do
find:blue replace:blue products
find:green replace green stuff
etc....

Thanks a lot
 
D

Don Guillett

See if this works
Sub replaceall()
With Range("e3:e6") 'myrng
.Replace What:="blue", Replacement:="black", LookAt:=xlWhole
.Replace What:="green", Replacement:="yellow", LookAt:=xlWhole
End With
End Sub
 
D

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????)
 
Top