find and replace

[InnaG]

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]

 

[Fluff]

Try

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]

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]

In that case remove the * from your fndList array

 

[InnaG]

thank you very much!!!!!!!!!!!!

 

[Fluff]

You're welcome & thanks for the feedback