find and replace

InnaG

New Member
Joined
Mar 18, 2019
Messages
19
Hello,
I have excel file with multiple tabs and I need to find text that contains "med ctr" or "med center" or "med cent" and replace it with "medical center" and this needs to be done on every sheet in the workbook. I found a couple of different codes but none of them works the way I need or I am getting error msg. Below is the latest code I tried.
Please help,

[FONT=&quot]Sub[/FONT][FONT=&quot] Multi_FindReplace()[/FONT]

[FONT=&quot]Dim[/FONT][FONT=&quot] sht [/FONT][FONT=&quot]As[/FONT][FONT=&quot] Worksheet[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] fndList [/FONT][FONT=&quot]As[/FONT][FONT=&quot] [/FONT][FONT=&quot]Variant[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] rplcList [/FONT][FONT=&quot]As[/FONT][FONT=&quot] [/FONT][FONT=&quot]Variant[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] x [/FONT][FONT=&quot]As[/FONT][FONT=&quot] [/FONT][FONT=&quot]Long[/FONT]

[FONT=&quot]fndList = Array("*MED CTR*", "*MED CENTER*", "*MED CENT*")[/FONT]
[FONT=&quot]rplcList = Array("MEDICAL CENTER")[/FONT]

[FONT=&quot]'Loop through each item in Array lists[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]For[/FONT][FONT=&quot] x = [/FONT][FONT=&quot]LBound[/FONT][FONT=&quot](fndList) [/FONT][FONT=&quot]To[/FONT][FONT=&quot] [/FONT][FONT=&quot]UBound[/FONT][FONT=&quot](fndList)[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]'Loop through each worksheet in ActiveWorkbook[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]For[/FONT][FONT=&quot] [/FONT][FONT=&quot]Each[/FONT][FONT=&quot] sht [/FONT][FONT=&quot]In[/FONT][FONT=&quot] ActiveWorkbook.Worksheets[/FONT]
[FONT=&quot] sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _[/FONT]
[FONT=&quot] LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _[/FONT]
[FONT=&quot] SearchFormat:=False, ReplaceFormat:=False[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Next[/FONT][FONT=&quot] sht[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Next[/FONT][FONT=&quot] x[/FONT]

[FONT=&quot]End[/FONT][FONT=&quot] [/FONT][FONT=&quot]Sub

The last part is were I am getting an error msg.
Thanks

[/FONT]
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,480
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As String
Dim x As Long

fndList = Array("*MED CTR*", "*MED CENTER*", "*MED CENT*")
rplcList = "MEDICAL CENTER"

'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
   'Loop through each worksheet in ActiveWorkbook
   For Each sht In ActiveWorkbook.Worksheets
      sht.Cells.Replace What:=fndList(x), Replacement:=rplcList, _
         LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
         SearchFormat:=False, ReplaceFormat:=False
   Next sht

Next x

End Sub
 

InnaG

New Member
Joined
Mar 18, 2019
Messages
19
It works but it replaces everything including medical center name. I probably did not explain myself correctly.
here is example of some of the information I am receiving:

Alpha Med Ctr
Robert Med Center
Golden Med Cent

I need it to be
Alpha Medical Center
Robert Medical Center
Golden Medical Center

Does this makes sense?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,480
Office Version
  1. 365
Platform
  1. Windows
In that case remove the * from your fndList array
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,480
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,276
Messages
5,527,732
Members
409,786
Latest member
AbdulMoix

This Week's Hot Topics

Top