Find and Replace Help

M

Moneyball Wilson

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
 
D

Dave Peterson

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?
 
D

Don Guillett

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find and replace multiple values in one statement 3
Macro to select columns and replace data 0
Need help with a "If" "Else" VB code 4
Help with a loop please. 10
auto run macro on cell change 2
Using Cell.Replace 1
Can I improve processing speed of macro 4
Limits of Substitute and Replace functions 4

Top