VBA to replace text in a single column

[reactionhank]

Hi all,

I'm trying to write a macro to replace string values in a single column. Basically each month I receive an excel file with more than 50 000 rows and I have to replace the text in one of the columns in order to match them to another file.

The issue is that there are more than 1 000 unique string values, so I've found this syntax, which lets me put all the old and new values in a table and have the macro feed from it:

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

The only problem is that it is currently searching through all columns from all sheets which slows it down incredibly. So I'm looking for help to modify it so that it feeds from a table in 1 sheet and replaces the values in a specific column in another (or the same one, doesnt matter).

All help is much appreciated. I have no experience with VBA, and am using Office 2016 if that matters.

[Bob Phillips]

Is this what you want?

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 cell As Range
Dim tbl As ListObject
'Designate Columns for Find/Replace data
Const fndList As Long = 1
Const rplcList = 2

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

    tbl.DataBodyRange.Replace What:=fndList, _
                              Replacement:=rplcList, _
                              LookAt:=xlPart, _
                              SearchOrder:=xlByRows, _
                              MatchCase:=False, _
                              SearchFormat:=False, _
                              ReplaceFormat:=False
End Sub