ExcelBanter - Find and Replace Help

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Find and Replace Help (https://www.excelbanter.com/excel-programming/417373-find-replace-help.html)

Moneyball Wilson

Find and Replace Help

Hi All,

I have a quick question...I written this Macro to find and replace a
specific range of cells but it is not working correctly.

The range of cells will highlight, but then nothing is replaced.
---------------------------------------------------------------------------------------

Sub Convert_To_Lower_Case_and_Replace()
' Loop to cycle through each cell in the specified range.
For Each x In Range("B2:B500")
x.Value = LCase(x.Value)
Next

Range("B2:B500").Select
Selection.Replace What:="vd", Replacement:="5-bmlp-5/8",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="br", Replacement:="5-btva-5/9",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="calx", Replacement:="5-nfld-5/11",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="chrtn", Replacement:="5-dres-5/12",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="clntn", Replacement:="5-wodsk-5/13",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dd", Replacement:="5-indy-5/14",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="har", Replacement:="5-may-5/15",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dela", Replacement:="5-sar-5/16",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="duq", Replacement:="5-bmlp-5/17",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="epr", Replacement:="5-wodsk-5/18",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="fhld", Replacement:="5-nfld-5/19",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="frd", Replacement:="5-btva-5/20",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="frdtn", Replacement:="5-dres-5/21",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="geod", Replacement:="5-hp-5/22",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="gosh", Replacement:="5-dres-5/23",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="har", Replacement:="5-may-5/24",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="hm", Replacement:="5-bmlp-5/25",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="hnvr", Replacement:="5-wodsk-5/26",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="ind", Replacement:="5-sar-5/7",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="leb", Replacement:="5-nor-5/28",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="lex", Replacement:="5-bmlp-5/29",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="mea", Replacement:="5-sar-5/30",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="mr", Replacement:="5-lon-5/31",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="np", Replacement:="5-nfld-5/32",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="od", Replacement:="5-kd-5/33",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="pcd", Replacement:="5-hp-5/34",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="prc", Replacement:="5-hp-5/35",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="prm", Replacement:="5-btva-5/36",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="rcr", Replacement:="5-sar-5/37",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="ridc", Replacement:="5-sar-5/38",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="scar", Replacement:="5-wodsk-5/39",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="scd", Replacement:="5-indy-5/40",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dd", Replacement:="5-indy-5/41",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="sudby", Replacement:="5-nor-5/42",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="summ", Replacement:="5-wodsk-5/43",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="trr", Replacement:="5-kd-5/44",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="truro", Replacement:="5-btva-5/45",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="vd", Replacement:="5-bmlp-5/46",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="yr", Replacement:="5-rp-5/47",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

Any help would be appreciated.

Sincerely,
Rambo

Dave Peterson

Find and Replace Help

My initial guess is that there is something else in the cells besides the value
(like "vd"). And since you're looking at xlwhole, that causes the problem.

If you try changing all the xlwhole's to xlpart's, does it work?

Moneyball Wilson wrote:

Hi All,

I have a quick question...I written this Macro to find and replace a
specific range of cells but it is not working correctly.

The range of cells will highlight, but then nothing is replaced.
---------------------------------------------------------------------------------------

Sub Convert_To_Lower_Case_and_Replace()
' Loop to cycle through each cell in the specified range.
For Each x In Range("B2:B500")
x.Value = LCase(x.Value)
Next

Range("B2:B500").Select
Selection.Replace What:="vd", Replacement:="5-bmlp-5/8",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="br", Replacement:="5-btva-5/9",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="calx", Replacement:="5-nfld-5/11",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="chrtn", Replacement:="5-dres-5/12",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="clntn", Replacement:="5-wodsk-5/13",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dd", Replacement:="5-indy-5/14",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="har", Replacement:="5-may-5/15",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dela", Replacement:="5-sar-5/16",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="duq", Replacement:="5-bmlp-5/17",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="epr", Replacement:="5-wodsk-5/18",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="fhld", Replacement:="5-nfld-5/19",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="frd", Replacement:="5-btva-5/20",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="frdtn", Replacement:="5-dres-5/21",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="geod", Replacement:="5-hp-5/22",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="gosh", Replacement:="5-dres-5/23",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="har", Replacement:="5-may-5/24",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="hm", Replacement:="5-bmlp-5/25",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="hnvr", Replacement:="5-wodsk-5/26",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="ind", Replacement:="5-sar-5/7",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="leb", Replacement:="5-nor-5/28",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="lex", Replacement:="5-bmlp-5/29",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="mea", Replacement:="5-sar-5/30",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="mr", Replacement:="5-lon-5/31",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="np", Replacement:="5-nfld-5/32",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="od", Replacement:="5-kd-5/33",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="pcd", Replacement:="5-hp-5/34",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="prc", Replacement:="5-hp-5/35",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="prm", Replacement:="5-btva-5/36",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="rcr", Replacement:="5-sar-5/37",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="ridc", Replacement:="5-sar-5/38",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="scar", Replacement:="5-wodsk-5/39",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="scd", Replacement:="5-indy-5/40",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dd", Replacement:="5-indy-5/41",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="sudby", Replacement:="5-nor-5/42",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="summ", Replacement:="5-wodsk-5/43",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="trr", Replacement:="5-kd-5/44",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="truro", Replacement:="5-btva-5/45",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="vd", Replacement:="5-bmlp-5/46",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="yr", Replacement:="5-rp-5/47",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

Any help would be appreciated.

Sincerely,
Rambo

--

Dave Peterson

Don Guillett

Find and Replace Help

Don Guillett

Find and Replace Help

I forgot to add an end with after you get thru

end with
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

Shouldn't be necessary to convert to lower case or select
Sub Convert_To_Lower_Case_and_Replace()

with Range("B2:B500")
.Replace "vd", "5-bmlp-5/8",LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
'now easier
.Replace "br", "5-btva-5/9"
.replace "calx",="5-nfld-5/11"
'etc for the rest
''''''======
ReplaceFormat:=False
Selection.Replace What:="chrtn", Replacement:="5-dres-5/12",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="clntn", Replacement:="5-wodsk-5/13",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dd", Replacement:="5-indy-5/14",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="har", Replacement:="5-may-5/15",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dela", Replacement:="5-sar-5/16",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="duq", Replacement:="5-bmlp-5/17",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="epr", Replacement:="5-wodsk-5/18",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="fhld", Replacement:="5-nfld-5/19",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="frd", Replacement:="5-btva-5/20",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="frdtn", Replacement:="5-dres-5/21",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="geod", Replacement:="5-hp-5/22",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="gosh", Replacement:="5-dres-5/23",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="har", Replacement:="5-may-5/24",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="hm", Replacement:="5-bmlp-5/25",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="hnvr", Replacement:="5-wodsk-5/26",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="ind", Replacement:="5-sar-5/7",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="leb", Replacement:="5-nor-5/28",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="lex", Replacement:="5-bmlp-5/29",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="mea", Replacement:="5-sar-5/30",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="mr", Replacement:="5-lon-5/31",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="np", Replacement:="5-nfld-5/32",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="od", Replacement:="5-kd-5/33",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="pcd", Replacement:="5-hp-5/34",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="prc", Replacement:="5-hp-5/35",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="prm", Replacement:="5-btva-5/36",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="rcr", Replacement:="5-sar-5/37",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="ridc", Replacement:="5-sar-5/38",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="scar", Replacement:="5-wodsk-5/39",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="scd", Replacement:="5-indy-5/40",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dd", Replacement:="5-indy-5/41",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="sudby", Replacement:="5-nor-5/42",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="summ", Replacement:="5-wodsk-5/43",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="trr", Replacement:="5-kd-5/44",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="truro", Replacement:="5-btva-5/45",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="vd", Replacement:="5-bmlp-5/46",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="yr", Replacement:="5-rp-5/47",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Moneyball Wilson" wrote in message
...
Hi All,

I have a quick question...I written this Macro to find and replace a
specific range of cells but it is not working correctly.

The range of cells will highlight, but then nothing is replaced.
---------------------------------------------------------------------------------------

Sub Convert_To_Lower_Case_and_Replace()
' Loop to cycle through each cell in the specified range.
For Each x In Range("B2:B500")
x.Value = LCase(x.Value)
Next

Range("B2:B500").Select
Selection.Replace What:="vd", Replacement:="5-bmlp-5/8",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="br", Replacement:="5-btva-5/9",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="calx", Replacement:="5-nfld-5/11",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="chrtn", Replacement:="5-dres-5/12",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="clntn", Replacement:="5-wodsk-5/13",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dd", Replacement:="5-indy-5/14",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="har", Replacement:="5-may-5/15",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dela", Replacement:="5-sar-5/16",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="duq", Replacement:="5-bmlp-5/17",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="epr", Replacement:="5-wodsk-5/18",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="fhld", Replacement:="5-nfld-5/19",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="frd", Replacement:="5-btva-5/20",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="frdtn", Replacement:="5-dres-5/21",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="geod", Replacement:="5-hp-5/22",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="gosh", Replacement:="5-dres-5/23",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="har", Replacement:="5-may-5/24",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="hm", Replacement:="5-bmlp-5/25",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="hnvr", Replacement:="5-wodsk-5/26",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="ind", Replacement:="5-sar-5/7",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="leb", Replacement:="5-nor-5/28",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="lex", Replacement:="5-bmlp-5/29",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="mea", Replacement:="5-sar-5/30",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="mr", Replacement:="5-lon-5/31",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="np", Replacement:="5-nfld-5/32",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="od", Replacement:="5-kd-5/33",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="pcd", Replacement:="5-hp-5/34",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="prc", Replacement:="5-hp-5/35",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="prm", Replacement:="5-btva-5/36",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="rcr", Replacement:="5-sar-5/37",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="ridc", Replacement:="5-sar-5/38",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="scar", Replacement:="5-wodsk-5/39",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="scd", Replacement:="5-indy-5/40",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="dd", Replacement:="5-indy-5/41",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="sudby", Replacement:="5-nor-5/42",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="summ", Replacement:="5-wodsk-5/43",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="trr", Replacement:="5-kd-5/44",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="truro", Replacement:="5-btva-5/45",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="vd", Replacement:="5-bmlp-5/46",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="yr", Replacement:="5-rp-5/47",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

Any help would be appreciated.

Sincerely,
Rambo

All times are GMT +1. The time now is 09:10 AM.