Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Find and Replace Help
 
 
LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default 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


Reply With QuoteReply With Quote
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Threaded Mode Threaded Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and Replace - Replace with Blank Space Studebaker Excel Discussion (Misc queries) 4 April 3rd 23 10:55 AM
Find/Replace Event or Find/Replace for Protected Sheet ... Joe HM Excel Programming 2 October 27th 07 03:55 PM
find and replace - replace data in rows to separated by commas msdker Excel Worksheet Functions 1 April 15th 06 01:00 AM
Using Find and Replace to replace " in a macro snail30152 Excel Programming 1 April 13th 06 11:58 PM
Replace method - cannot find any data to replace Mike Excel Programming 5 April 6th 06 08:56 PM


All times are GMT +1. The time now is 03:29 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement