Array loop keeps replacing data

[lthldc]

Hey people,

Could I ask some help on this please. I have put together a replace macro that reads a table to from an array to then find and replace data. Problem is, it keeps looping and replacing the data over and over.

Help would be apprecited. I'm basiclly searching a list of 1-4 digit numbers and replacing it with a barcode number. My table is kind of like "220" "R0220354878". But as the search loops and finds other numbers it keeps replacing over and over. I have 570 numbers to assign barcodes to. Any help would be appreciated as I don't want to have to manually do it with find/replace in excel due to the amount of data in the sheets.

Sub Multi_FindReplace()
Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Dim ReplaceCount As Long


  Set tbl = Worksheets("sh2").ListObjects("Table1")

  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
  
  fndList = 1
  rplcList = 2
  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
              
              ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")
          
       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
    
MsgBox "I have replaced " & ReplaceCount & " cell(s)."
End Sub

 

[lthldc]

I basiclly just need it to do 1 sweep of the sheet so it doesn't start finding the numbers within the barcode numbers and keep replacing them until it runs out of combinations.

 

[Fluff]

Hi & welcome to MrExcel
Try changing this

LookAt:=xlPart

to

LookAt:=xlWhole

 

[lthldc]

Kew I was close, Thank you very much, working perfectly!

 

[Fluff]

Glad to help & thanks for the feedback