ExcelBanter - View Single Post

Dave Peterson

Ahhh.

RB Smissaert wrote:

OK, the problem wasn't that well described.
I thought he was trying to replace CR if that was the only text in the cell.

RBS

"Dave Peterson" wrote in message
...
But that means you're looking at the whole cell--not whole word (like
MSWord).

RB Smissaert wrote:

Try replacing in your code :=xlPart with :=xlWhole

RBS

"icdoo" wrote in
message ...

I needed to search a cell for "CR" and change it to "Crescent". Problem
was if the the cell contained "Crestlawn CR" it would change it to
"CRESCENTestlawn CRESCENT". I found away around it by adding my name
"malcolmbrown" to the end of each cell and then searching for
CRMALCOLMBROWN". IT works but there has to be a better way.

here is part of my routine so far

'Loops adding MALCOLMBROWN at the end of each address
Range("A1").Select
Dim p As Integer
intRowCount = Range("A1").CurrentRegion.Rows.Count
For p = 1 To intRowCount
'Adds MALCOLMBROWN onto the end of the address
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0,
1).Value & "MALCOLMBROWN"
ActiveCell.Offset(1, 0).Select
Next p
'Replaces CRMALCOLMBROWN with crescent and deletes
MALCOLMBROWN
Columns("B:B").Select
Selection.Replace What:="CRMALCOLMBROWN",
Replacement:="CRESCENT", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="MALCOLMBROWN", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

--
icdoo
------------------------------------------------------------------------
icdoo's Profile:
http://www.excelforum.com/member.php...o&userid=27342
View this thread:
http://www.excelforum.com/showthread...hreadid=468512

--

Dave Peterson

--

Dave Peterson