Well, only you can create the list, as you know the find/replace values.
So in a suitable place, in 2 columns (say starting from A1:B1) enter the
data; A column for the Find values, B column for Replace values.
Select all these values in Column A and give the range a name
(InsertNameDefine), say "rngData".

Then in your code, you can:
For Each Cell In Worksheet("NotThisSheet").Range("rngData")
Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0,1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next

Note that "Cell" will work its way down your list of Find values, replacing
it (say "*Atlanta*"), with the value in its row of the Replace values (say
"Atlanta").
Also, "Cells" refers to all the cells in the activesheet. You could use
ActiveSheet.UsedRange instead.

NickHK

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