Find and Replace Multiple Strings in Excel

Find and Replace Multiple Strings in Excel

January 11, 2020

The below VBA code, posted here by the Spreadsheet Guru, can be used to find and replace multiple values in an Excel workbook.   List the terms you want to find in column A of 'Sheet1' and the terms they should replace in column B.   Select the ranges (not the whole columns) and then press CTRL + T to add the data as 'Table1', the default name. 

 

Put the VBA code in a new module. 

 

  

 

 

Go to View . . . Macros and run the code. 

 The macro will replace anywhere the listed terms appear on other worksheets.   So this worksheet: 

 . . . is changed to:

 

 This macro will work for strings which don't consist of the entire contents of a cell. 

 

 

Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant

'Create variable to point to your table
  Set tbl = Worksheets("Sheet1").ListObjects("Table1")

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

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

End Sub

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com