Re: Find & Replace macro limit?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
(Insert>Name>Define), 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" <RS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:70C7EDEE-3D3B-43B9-B0D3-A00996B3A804@xxxxxxxxxxxxxxxx
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" <RS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:92D12B16-3316-4045-8A92-3E58ADD3B711@xxxxxxxxxxxxxxxx
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?





.

Relevant Pages

  • Re: Replacing Excel Add-In
    ... variant array with the formula for each cell, ... replacing them, I had #NA! ... >That should run MUCH faster than a loop. ...
    (microsoft.public.excel.programming)
  • Problems Defining Object Variable in For Each Loop
    ... Normally I would build the loop as follows: ... For each cell in Worksheet ... expecting to see the same sheet name. ...
    (microsoft.public.excel.programming)
  • benchmark on rewrited pde1.frt
    ... Benchmarks for example exposed in pde1.frt(rectangular domenium) by ... LOOP structure -> the word ... CREATE SUBDOM #SUBDOM 2* CELLS ALLOT ... CELL +LOOP ...
    (comp.lang.forth)
  • RE: User List Box - List from Hidden range - VBA worng
    ... In your for loop, ... range is only 1 column wide this will be a single cell). ... Basic Editor type the word on it's own in lower case on a line and hit enter. ... in the array reference - and an array starts with element 0. ...
    (microsoft.public.excel.programming)
  • Re: Problem with Do Loop and If
    ... No loop required. ... > reaches the first blank cell. ... >> Sub testIt1() ... >> Excel, PowerPoint, and VBA add-ins, tutorials ...
    (microsoft.public.excel.programming)