0

I'm trying to run a script that replaces certain words/numbers with other words/numbers. It works fine, but the problem is it should only run on column G because otherwise things would be replaced that shouldn't be replaced.

The code looks like this:

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

fndList = Array("10780", "10782", "10783", "10784", "10785", "10786", "10787", "10789", "10790", "10791", "10792", "10793", "10794", "10795", "10796", "10797", "10798", "10799", "10800", "10801", "10802", "10803", "10804", "10805", "10806")
rplcList = Array("90310011", "90310012", "90310020", "90310023", "90310039", "90310044", "90310051", "90310054", "90310061", "90310066", "90310079", "90310096", "90310099", "90310100", "90310101", "90310113", "90310119", "90310143", "90310148", "90310150", "90310154", "90310159", "90310176", "90310177", "90310161")

'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

What needs to be changed so that the code only runs on column G?

1

Try replacing this line:

sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _

With this line:

sht.Range("G:G").Replace What:=fndList(x), Replacement:=rplcList(x), _


sht.Cells operates on all the cells on the sheet, but sht.Range("G:G") operates only on the cells in the specified range, which in this case is column G.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.