VBA CODE to replace cell content based on conditions in 2 different columns

Question

I have a problem on the code that I am writing. I have researched multiple websites that has similar coding scenario like this, some of them gave some insight on how to write this code but it only does half the job.

So in Col B, I have a list of Routing guides: ABC, DEF, GHI In Col C, I also have an array of information: VVV, WWW, XXX, YYY, ZZZ.

If it says ABC in Col B and Col C shows WWW, XXX or YYY, I need to replace Col C with UUU. Below is my code and I appreciate your help in advance! Thank you!

Sub Replace_cell ()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

'Filter ABC  only on Routing Column B

Selection.AutoFilter
    ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:= _
        "ABC"

'Find WWW, XXX, YYY in Col C and replace them with UUU

fndList = Array("WWW", "XXX", "YYY")
rplcList = ("UUU")

'Loop through each item in Array lists

For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
    For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _     
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
    Next sht

Next x

End Sub

Thank you!

Answer 1

You have to do the filtering for each worksheet in turn, and then find/replace, so the nesting order (inner/outer) of your loops should be reversed. Also as you said you want to replace in Column C, not all cells of the worksheet.

Sub Replace_colC()
  Dim sht As Worksheet, x As Long, fndList, rplcList

  fndList = Array("WWW", "XXX", "YYY")
  rplcList = Array("UUU", "UUU", "UUU")
  ' though replacement values are the same, they could be different

  For Each sht In ActiveWorkbook.Worksheets
   'Filter ABC on Routing Column B
    sht.Cells.AutoFilter Field:=2, Criteria1:="ABC"

    'Loop through each item in Array lists and replace
    For x = LBound(fndList) To UBound(fndList)
      sht.UsedRange.Columns("C").Replace What:=fndList(x), Replacement:=rplcList(x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
    Next x
    sht.AutoFilterMode = False
  Next sht
End Sub