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

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

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

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

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

It looks like you want to replace " CR" at the end of each value in column B.

If that's the case, maybe something like:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myFStr As String
Dim myTStr As String
Dim FoundCell As Range

With Worksheets("sheet1")
Set myRng = .Range("b:b")
End With

myFStr = " CR"
myTStr = " Crescent"

With myRng
Do
Set FoundCell = .Cells.Find(What:="*" & myFStr, _
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do 'we're done
End If

FoundCell.Value _
= Left(FoundCell.Value, _
Len(FoundCell.Value) - Len(myFStr)) & myTStr
Loop
End With

End Sub

It looks for "* CR" in the whole cell. That means that the cell has to end with
spaceCR. Once we find it, we change it and keep looking.




icdoo wrote:

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