About Us

PSM[_10_]

How can I tidy up this code as it goes on for 50+ searches ?

' Update_C5&Mgr (Select Columns)
Range("A:B").Select
Range(Selection, Selection.End(xlDown)).Select

' Update_C5 (Column A)
Selection.Replace What:="*01", _
Replacement:="01", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="*07", _
Replacement:="07", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="*08", _
Replacement:="08", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="*09", _
Replacement:="09", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False

--
PSM

joel

ReplaceStr = Array("01,"07","08","09")

' Update_C5&Mgr (Select Columns)
Range("A:B").Select
Range(Selection, Selection.End(xlDown)).Select

for each Num in ReplaceStr
Selection.Replace _
What:="*" & Num, _
Replacement:=Num, _
LookAt:=xlPart

next Num

"PSM" wrote:

How can I tidy up this code as it goes on for 50+ searches ?

' Update_C5&Mgr (Select Columns)
Range("A:B").Select
Range(Selection, Selection.End(xlDown)).Select

' Update_C5 (Column A)
Selection.Replace What:="*01", _
Replacement:="01", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="*07", _
Replacement:="07", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="*08", _
Replacement:="08", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="*09", _
Replacement:="09", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False

--
PSM

Don Guillett

Should be pretty easy. It would help if you post some of the data

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PSM" wrote in message
...

How can I tidy up this code as it goes on for 50+ searches ?

' Update_C5&Mgr (Select Columns)
Range("A:B").Select
Range(Selection, Selection.End(xlDown)).Select

' Update_C5 (Column A)
Selection.Replace What:="*01", _
Replacement:="01", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="*07", _
Replacement:="07", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="*08", _
Replacement:="08", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="*09", _
Replacement:="09", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False

--
PSM

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Multiple find mulitple replace	spaceage	Excel Worksheet Functions	1	March 29th 07 11:42 AM
Find/Replace in multiple worksheets	Lynn	Excel Discussion (Misc queries)	1	March 22nd 07 04:17 PM
find replace in multiple sheets based on namebox	gabitzu	Excel Discussion (Misc queries)	2	December 19th 06 11:30 AM
Multiple Find and Replace in one function	[email protected]	Excel Worksheet Functions	4	September 11th 06 03:16 AM
How can I do a find and replace for a cell that has multiple line.	yrat	Excel Discussion (Misc queries)	1	April 19th 05 03:48 PM

Menu

About Us