Dear Nick,

Since I'm new to VB coding, how would I do this? The problem that I have is
that the imported spreadsheet could have some different names every time and
those names appear can appear multiple times randomly within a column.
Sounds like what you're saying is for me to somehow create a list of these
names and their associated replacements, put it on a separate worksheet, and
then run your code?

Is there a way to easily create such a list? If so, how? You also talk
about "entering all the names in a range and do the replace in a loop." What
do you mean by doing the replace in a loop? How do you do that? I'm
assuming your code does the loop?

In the second line of your code,
Cells.Replace What:=Cell.Value & "*", Replacement:=" " & Cell.Value,

where would I enter multiple values? For example, replacing Atlanta* with
Atlanta and Boston* with Boston, etc.?

"NickHK" wrote:

Nothing wrong with the code as such.
No reason why there is a limit, as they are each a separate statement.

But you may find it easier entering all the names in a range and do the
replace in a loop.
Make sure the names are not the same worksheet (or they will get replaced
also), or limit your replacement to a range instead Cells.

Dim Cell As Range

For Each Cell In Worksheet("NotThisSheet").Range("A1:A20")
Cells.Replace What:=Cell.Value & "*", Replacement:=" " & Cell.Value,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next

NickHK

"RS" wrote in message
...
I'm 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?