Find and replace multiple values with other values in a range

[sofas]

Welcome. I try to search for specific values in a range and replace them with other values when checking their presence. I tried to implement this using the following code, but it did not work. I hope to find someone to help me with this

Option Explicit
Sub CleanUpOutlier()

Dim lr&, i&, j&, rng, oldData, newData
lr = Cells(Rows.Count, "C").End(xlUp).Row
rng = Range("E11:Q" & lr).Value
oldData = Array("t1", "t566", "m45", "w78", "tr2")
newData = Array("machine1", "machine15", "machine166", "machine166", "machine19")
For i = 1 To lr
    For j = 0 To UBound(oldData)
      If rng Like oldData(j) Then rng = newData(j)
    Next
Next
Range("E11:Q" & lr).Value = rng
End Sub

 

[Akuini]

This is untested:

Sub CleanUpOutlier_2()

Dim lr&, i&, j&, oldData, newData
Dim rng As Range
lr = Cells(Rows.Count, "C").End(xlUp).Row
oldData = Array("t1", "t566", "m45", "w78", "tr2")
newData = Array("machine1", "machine15", "machine166", "machine166", "machine19")
Application.ScreenUpdating = False
For Each rng In Range("E11:Q" & lr)
    For j = 0 To UBound(oldData)
      If rng Like oldData(j) Then rng = newData(j)
    Next
Next
Application.ScreenUpdating = True
End Sub

If it's not working, please share some examples. If it's working but slow, we can modify the code to improve its speed.

 

[Rick Rothstein]

Give this code a try...

Sub Test()
  Dim X As Long, lr As Long, Rng As Range
  Dim oldData As Variant, newData As Variant
  lr = Cells(Rows.Count, "C").End(xlUp).Row
  Set Rng = Range("E11:Q" & lr)
  oldData = Array("t1", "t566", "m45", "w78", "tr2")
  newData = Array("machine1", "machine15", "machine166", "machine166", "machine19")
  For X = LBound(oldData) To UBound(oldData)
    Rng.Replace oldData(X), newData(X), xlWhole, , , , False, False
  Next
End Sub

 

[sofas]

This is untested:

Sub CleanUpOutlier_2()

Dim lr&, i&, j&, oldData, newData
Dim rng As Range
lr = Cells(Rows.Count, "C").End(xlUp).Row
oldData = Array("t1", "t566", "m45", "w78", "tr2")
newData = Array("machine1", "machine15", "machine166", "machine166", "machine19")
Application.ScreenUpdating = False
For Each rng In Range("E11:Q" & lr)
    For j = 0 To UBound(oldData)
      If rng Like oldData(j) Then rng = newData(j)
    Next
Next
Application.ScreenUpdating = True
End Sub

If it's not working, please share some examples. If it's working but slow, we can modify the code to improve its speed.

Thank you. I tried to modify it as follows. It worked fine for me, but I may need something faster when there is a large amount of data

Sub CleanUpOutlier_3()
Dim lr&, i&
Dim oldData() As Variant, newData() As Variant

Set WS = Sheet1

lr = WS.Cells.SpecialCells(xlCellTypeLastCell).Row
oldData = Array("t1", "t566", "m45", "w78", "tr2")
newData = Array("machine1", "machine15", "machine166", "machine166", "machine19")
Application.ScreenUpdating = False
    For i = LBound(oldData) To UBound(oldData)
        WS.Range("E11:Q" & lr).Replace _
            What:=oldData(i), Replacement:=newData(i), _
            SearchOrder:=xlByColumns, MatchCase:=True
Next
Application.ScreenUpdating = True
End Sub

 

[Akuini]

It worked fine for me, but I may need something faster when there is a large amount of data

I think your last code, which is base on @Rick Rothstein code, should be pretty fast.
About how many data rows are you talking about?

 

[sofas]

Give this code a try...

Sub Test()
  Dim X As Long, lr As Long, Rng As Range
  Dim oldData As Variant, newData As Variant
  lr = Cells(Rows.Count, "C").End(xlUp).Row
  Set Rng = Range("E11:Q" & lr)
  oldData = Array("t1", "t566", "m45", "w78", "tr2")
  newData = Array("machine1", "machine15", "machine166", "machine166", "machine19")
  For X = LBound(oldData) To UBound(oldData)
    Rng.Replace oldData(X), newData(X), xlWhole, , , , False, False
  Next
End Sub

Thank you. After experimenting, I noticed that it is somewhat faster than my code. I appreciate you helping me.

 

[Rick Rothstein]

Thank you. After experimenting, I noticed that it is somewhat faster than my code. I appreciate you helping me.

Turning off screen updating might make is a little faster...

Sub Test()
  Dim X As Long, lr As Long, Rng As Range
  Dim oldData As Variant, newData As Variant
  lr = Cells(Rows.Count, "C").End(xlUp).Row
  Set Rng = Range("E11:Q" & lr)
  oldData = Array("t1", "t566", "m45", "w78", "tr2")
  newData = Array("machine1", "machine15", "machine166", "machine166", "machine19")
  Application.ScreenUpdating = False
  For X = LBound(oldData) To UBound(oldData)
    Rng.Replace oldData(X), newData(X), xlWhole, , , , False, False
  Next
  Application.ScreenUpdating = True
End Sub

 

[sofas]

I think your last code, which is base on @Rick Rothstein code, should be pretty fast.
About how many data rows are you talking about?

Yes, it is very remarkably fast, but I think that Rick Rothstein's code is a little faster after testing it on approximately 1020 rows of data.

 

[Rick Rothstein]

I am not sure if our messages crossed in the ether or not... did you see my last message where I included code that turned off (and back on, of course) the screen updating?