Macro to find & replace from source table - need to match whole source table string to partial string in workbook

[yruLikeThis]

I am using a macro that will use table named "S4A_List" on "List" worksheet/tab to find data in column 1 of table and replace with data in column 2 throughout all the other sheets in the workbook. The issue is that column 2 sometimes matches data in column 1 with extra number(s) at end, so after it replaces a row, it finds it again in part of another row (see image: after replacing 890011 with 6001300, the macro gets to row 8184 and finds "600130" inside the 6001300 it just replaced, and replaces it again with 65039000 (with an extra "0" at the end because it only replaced the first part of the match)

I changed the code from LookAt:=Part to LookAt:=xlWhole but now it's not replacing 890011 if it's inside of a formula or combined with any other characters in a cell. I want to match the whole string from the table to partial strings in the worksheet, if that makes sense. Pasting whole code below. Thanks in advance.

Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Dim TempArray As Range
Dim X As Long


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


'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:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

End If
Next sht
Next X


End Sub

 

[Kaper]

This approach could be somewhat slower (It's amazing how quick Cells.Replace is), but shall do the job.

start with loading replacements table,
then loop by sheets and in this loop:
load current content into original array,
copy it to after-replacement array
loop for each repalcement array pair
look for to-be-repalced content in original array
if found replace in after-replacement array
copy after-replacement array back to a sheet and take next sheet.

The important part is to do search in one and replacement in the second array.
I'm not writing a code, but I hope you can easily do it yourself.

BTW - using CODE tags on forum make code more readable