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?

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?

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?

I tried what you said, but how do I invoke this? When I go to Macros, I
dont see this. Also, I added the code to a particular sheet, should it be
somewhere else? Here is my code:
Dim Cell As Range

For Each Cell In Worksheet("Codes").Range("rngData")
Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next

The data to be replaced is on a separate worksheet. Do I need to add some
code before the Dim Cell As Range line? You also said €œ€¦ "Cells" refers to
all the cells in the activesheet. You could use ActiveSheet.UsedRange
instead.€� Whats the difference? Dont they both mean the same thing
because they both say ActiveSheet at the beginning? Would I put
ActiveSheet.UsedRange right where Cells appears in the 2nd lind of code, as
such:
ActiveSheet.UsedRange.Replace What:=Cell.Value, Replacement:=Cell.Offset(0,
1).Value, _

Thanks for your help.

"NickHK" wrote:

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?

Check the help for Usedrange, Cells, etc.

Place a button on the worksheet containing the data to be fixed.
Double-click it. Enter this code in the signature provided.
Run.

And Yes, to the last part.

NickHK

"RS" wrote in message
...
I tried what you said, but how do I invoke this? When I go to Macros, I
don't see this. Also, I added the code to a particular sheet, should it
be
somewhere else? Here is my code:
Dim Cell As Range

For Each Cell In Worksheet("Codes").Range("rngData")
Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next

The data to be replaced is on a separate worksheet. Do I need to add some
code before the Dim Cell As Range line? You also said ". "Cells" refers
to
all the cells in the activesheet. You could use ActiveSheet.UsedRange
instead." What's the difference? Don't they both mean the same thing
because they both say ActiveSheet at the beginning? Would I put
ActiveSheet.UsedRange right where Cells appears in the 2nd lind of code,
as
such:
ActiveSheet.UsedRange.Replace What:=Cell.Value,
Replacement:=Cell.Offset(0,
1).Value, _

Thanks for your help.

"NickHK" wrote:

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, replac
ing
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?