NickHK
12/15/2006 9:18:00 AM
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" <RS@discussions.microsoft.com> wrote in message
news:D7067BE2-A472-4559-8251-F4BED3E48412@microsoft.com...
> 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
> > (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, 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" <RS@discussions.microsoft.com> wrote in message
> > news:70C7EDEE-3D3B-43B9-B0D3-A00996B3A804@microsoft.com...
> > > 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@discussions.microsoft.com> wrote in message
> > > > news:92D12B16-3316-4045-8A92-3E58ADD3B711@microsoft.com...
> > > > > 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?
> > > >
> > > >
> > > >
> >
> >
> >