VBA code to do find and replace on multiple values

[benjie1982]

Hi guys, need some help here. I have a list of values to find (column A) and replace withe new values Column B - over 400 rows. I need to run the find and replace over the whole workbook (or at least a whole sheet at a time)

I found alot of macros on the internet that seem to be very old and don't work.

This is one of the ones I found that looks like it will do but I keep getting a variable not defined error at 'Set TempArray'. My knowledge of code is very limited so I have no idea how to fix this. If someone could help me fix it or show me one of theirs that works, that'll be much appreciated

Sub Multi_FindReplace()

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("List").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

Thanks

 

[Peter_SSs]

I haven't checked the code to see if it will do what you want, but to fix that message, add 2 more Dim lines with the others at the start of the code.

Dim TempArray As Range
Dim X As Long

 

[benjie1982]

I haven't checked the code to see if it will do what you want, but to fix that message, add 2 more Dim lines with the others at the start of the code.

Dim TempArray As Range
Dim X As Long

Thanks. It got past that

I now get a subscript out of range error at this line:

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

I assumed "List" is the sheet name where the find values are and "Table1" i have no idea what that refers to

 

[benjie1982]

Actually figured it out. I had to set a table and name it Table1 for it work. Thanks for all the help

 

[Peter_SSs]

Thanks for all the help

You are welcome.