Batch Replace in Excel

[naveeddil]

Dear All,
I were look for batch replace addon and thus thus to this site got the VBA code. Now it provide two different codes

1. Replace one list of text with other list in your main table (do the job hidden)
2. Replace one text with another text (display a message popup that i've changed this much cells using COUNTIF)

What i want is, I want to modify the first code to display popup message after replacing one list of text with another
Both the codes below in order

Code 1: Replace one list text with another (NO message box)

[COLOR=#00007F]Sub[/COLOR] Multi_FindReplace()
[COLOR=#007F00]'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table[/COLOR]
[COLOR=#007F00]'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault[/COLOR]

[COLOR=#00007F]Dim[/COLOR] sht [COLOR=#00007F]As[/COLOR] Worksheet
[COLOR=#00007F]Dim[/COLOR] fndList [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Integer[/COLOR]
[COLOR=#00007F]Dim[/COLOR] rplcList [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Integer[/COLOR]
[COLOR=#00007F]Dim[/COLOR] tbl [COLOR=#00007F]As[/COLOR] ListObject
[COLOR=#00007F]Dim[/COLOR] myArray [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Variant[/COLOR]

[COLOR=#007F00]'Create variable to point to your table[/COLOR]
  [COLOR=#00007F]Set[/COLOR] tbl = Worksheets("Sheet1").ListObjects("Table1")

[COLOR=#007F00]'Create an Array out of the Table's Data[/COLOR]
  [COLOR=#00007F]Set[/COLOR] TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
  
[COLOR=#007F00]'Designate Columns for Find/Replace data[/COLOR]
  fndList = 1
  rplcList = 2

[COLOR=#007F00]'Loop through each item in Array lists[/COLOR]
  [COLOR=#00007F]For[/COLOR] x = [COLOR=#00007F]LBound[/COLOR](myArray, 1) [COLOR=#00007F]To[/COLOR] [COLOR=#00007F]UBound[/COLOR](myArray, 2)
    [COLOR=#007F00]'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)[/COLOR]
      [COLOR=#00007F]For[/COLOR] [COLOR=#00007F]Each[/COLOR] sht [COLOR=#00007F]In[/COLOR] ActiveWorkbook.Worksheets
        [COLOR=#00007F]If[/COLOR] sht.Name <> tbl.Parent.Name [COLOR=#00007F]Then[/COLOR]
          
          sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
        
        [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]If[/COLOR]
      [COLOR=#00007F]Next[/COLOR] sht
  [COLOR=#00007F]Next[/COLOR] x

[COLOR=#00007F]End[/COLOR] [COLOR=#00007F]Sub[/COLOR]

Code 2: with Message

[COLOR=#00007F]Sub[/COLOR] FindReplaceAll_CountReplacements()
[COLOR=#007F00]'PURPOSE: Find & Replace text/values throughout entire workbook, notify user of how many cells were affected[/COLOR]
[COLOR=#007F00]'SOURCE: www.TheSpreadsheetGuru.com[/COLOR]

[COLOR=#00007F]Dim[/COLOR] sht [COLOR=#00007F]As[/COLOR] Worksheet
[COLOR=#00007F]Dim[/COLOR] fnd [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Variant[/COLOR]
[COLOR=#00007F]Dim[/COLOR] rplc [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Variant[/COLOR]
[COLOR=#00007F]Dim[/COLOR] ReplaceCount [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Long[/COLOR]

fnd = "April"
rplc = "May"

[COLOR=#00007F]For[/COLOR] [COLOR=#00007F]Each[/COLOR] sht [COLOR=#00007F]In[/COLOR] ActiveWorkbook.Worksheets

  ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")

  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=[COLOR=#00007F]False[/COLOR], ReplaceFormat:=False
    
[COLOR=#00007F]Next[/COLOR] sht

MsgBox "I have completed my search and made replacements in " & ReplaceCount & " cell(s)."

[COLOR=#00007F]End[/COLOR] [COLOR=#00007F]Sub[/COLOR]

For ease I'm attaching an excel sheet as well with the macro and tables.