About Us

RS

How do I do what youre suggesting?

"Gary''s Student" wrote:

As coded it works. But you need only one Search. Put it in a loop and put
the strings into a string array.
--
Gary's Student

"RS" wrote:

Im working in Excel 2000 and want to know if there is a limit on the number
of items you can put in a Find & Replace macro. I have a spreadsheet that I
import that has different names appearing at various places within a column
and I want to replace those names in one step with items in my macro. I was
editing a Find & Replace macro to include more than one item at a time, but
after I entered 13 items, anything else I added turned a red color. Here is
the code I am working on:

Sub ComboTest()
'
' ComboTest Macro
'

'
Cells.Replace What:="Boulder*", Replacement:=" Boulder", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="Boston Area Net-Service", Replacement:="BANS", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="Colorado*", Replacement:="CBCS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="CRP*", Replacement:="CRP", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="California Case*", Replacement:="Cal Case",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="Dallas*", Replacement:=" Dallas", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="East Texas ARC", Replacement:="ETARC", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="ENACT INC", Replacement:="Enact", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="Great Falls*", Replacement:="GF", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="Harbor*", Replacement:="Harbor", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="Las Vegas*", Replacement:=" Las Vegas ",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="Sacramento*", Replacement:=" Sacramento", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="Washington*", Replacement:="Washington",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

As I mentioned before, trying to add any more Replace items causes the
new lines of code to turn red. Is there a way around this apparent limit?

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
Find & Replace - Limit search to a specific column	falena23	Excel Worksheet Functions	3	July 28th 08 03:46 PM
Find & Replace macro limit?	NickHK	Excel Programming	4	December 15th 06 09:18 AM
Using Find and Replace to replace " in a macro	snail30152	Excel Programming	1	April 13th 06 11:58 PM
find&replace macro	Elainey	Excel Worksheet Functions	0	January 6th 06 09:20 PM
Help with find and replace macro	davegb	Excel Programming	5	June 23rd 05 11:31 PM

Menu

About Us