Find & Replace values in All Sheets except the LookUp Sheet

[xlhelp15]

Hello Experts - Would need your kind assistance on the below to change values in all the sheet using VBA.

This is my table in LookUp sheet, would need find the list in Check_ID and replace it with respective File_ID in all sheets.

Kindly assist on this.

 

[Flaiban]

Try this code :

Sub Find_Replace()
    Dim wb As Workbook, ws As Worksheet
    Dim myList As Variant, myRange As Range
    Dim i As Long, msg As String
    
    ' find/replace list
    Set wb = ThisWorkbook ' or ActiveWorkbook
    myList = wb.Sheets("Sheet1").Range("A1:B238").Value2 'Sheet1 or Sheet Name
   
   
    For Each ws In wb.Sheets
        If ws.Name <> "Sheet1" Then 'Sheet1 or Sheet Name
            Set myRange = ws.Range("A1:AZ9999")
            For i = LBound(myList) To UBound(myList)
                If Len(myList(i, 1)) > 0 Then
                    myRange.Replace _
                      What:=myList(i, 1), _
                      Replacement:=myList(i, 2), _
                      LookAt:=xlWhole
                End If
            Next i
            msg = msg & vbCr & ws.Name
        End If
    Next
    MsgBox "Sheets processed :" & msg, vbInformation, wb.Name
End Sub

Suposing CheckID is column A / File_ID Column B

 

[jolivanes]

Sub Or_Maybe_So()
Dim rep, i As Long, sh1 As Worksheet, ws As Worksheet
Set sh1 = Worksheets("Sheet1")    '<---- Change as required
rep = sh1.Cells(1, 1).CurrentRegion.Value     '<---- Change as required
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> sh1.Name Then
        With ws.UsedRange
            For i = LBound(rep) + 1 To UBound(rep)
                .Replace rep(i, 1), rep(i, 2), 1
            Next i
        End With
        End If
    Next ws
End Sub

 

[xlhelp15]

Try this code :

Sub Find_Replace()
    Dim wb As Workbook, ws As Worksheet
    Dim myList As Variant, myRange As Range
    Dim i As Long, msg As String
   
    ' find/replace list
    Set wb = ThisWorkbook ' or ActiveWorkbook
    myList = wb.Sheets("Sheet1").Range("A1:B238").Value2 'Sheet1 or Sheet Name
  
  
    For Each ws In wb.Sheets
        If ws.Name <> "Sheet1" Then 'Sheet1 or Sheet Name
            Set myRange = ws.Range("A1:AZ9999")
            For i = LBound(myList) To UBound(myList)
                If Len(myList(i, 1)) > 0 Then
                    myRange.Replace _
                      What:=myList(i, 1), _
                      Replacement:=myList(i, 2), _
                      LookAt:=xlWhole
                End If
            Next i
            msg = msg & vbCr & ws.Name
        End If
    Next
    MsgBox "Sheets processed :" & msg, vbInformation, wb.Name
End Sub

Suposing CheckID is column A / File_ID Column B

It worked as a treat !!!! Thanks a lot !

 

[xlhelp15]

Sub Or_Maybe_So()
Dim rep, i As Long, sh1 As Worksheet, ws As Worksheet
Set sh1 = Worksheets("Sheet1")    '<---- Change as required
rep = sh1.Cells(1, 1).CurrentRegion.Value     '<---- Change as required
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> sh1.Name Then
        With ws.UsedRange
            For i = LBound(rep) + 1 To UBound(rep)
                .Replace rep(i, 1), rep(i, 2), 1
            Next i
        End With
        End If
    Next ws
End Sub

So easy to understand and worked as expected Thanks much

 

[xlhelp15]

It worked as a treat !!!! Thanks a lot !

Try this code :

Sub Find_Replace()
    Dim wb As Workbook, ws As Worksheet
    Dim myList As Variant, myRange As Range
    Dim i As Long, msg As String
  
    ' find/replace list
    Set wb = ThisWorkbook ' or ActiveWorkbook
    myList = wb.Sheets("Sheet1").Range("A1:B238").Value2 'Sheet1 or Sheet Name
 
 
    For Each ws In wb.Sheets
        If ws.Name <> "Sheet1" Then 'Sheet1 or Sheet Name
            Set myRange = ws.Range("A1:AZ9999")
            For i = LBound(myList) To UBound(myList)
                If Len(myList(i, 1)) > 0 Then
                    myRange.Replace _
                      What:=myList(i, 1), _
                      Replacement:=myList(i, 2), _
                      LookAt:=xlWhole
                End If
            Next i
            msg = msg & vbCr & ws.Name
        End If
    Next
    MsgBox "Sheets processed :" & msg, vbInformation, wb.Name
End Sub

Suposing CheckID is column A / File_ID Column B

@Flaiban - But landing on another issue - if you look at the Row5 - C253 has 042448. When i run the macro it changes C253 everywhere as 42448 instead of 042448. Is there a way to update the value as it is ?

 

[xlhelp15]

So easy to understand and worked as expected Thanks much

@jolivanes - But landing on another issue - if you look at the Row5 - C253 has 042448. When i run the macro it changes C253 everywhere as 42448 instead of 042448. Is there a way to update the value as it is ?

 

[jolivanes]

If the values in Column B end up in the array as displayed, this works.
Change this

.Replace rep(i, 1), rep(i, 2), 1

to this

.Replace rep(i, 1), "'" & rep(i, 2), 1