☰

为 Excel 查找和替换脚本指定特定列

tag-icon • tag-icon microsoft-excel vba find-and-replace
为 Excel 查找和替换脚本指定特定列

我正在尝试运行一个脚本，用其他单词/数字替换某些单词/数字。它运行正常，但问题是它只能在 G 列上运行，否则会替换掉不该替换的东西。

代码如下：

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

需要进行哪些更改才能使代码仅在 G 列运行？

答案1

尝试替换此行：

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

使用以下行：

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


sht.Cells对工作表上的所有单元格进行操作，但sht.Range("G:G")仅对指定范围内的单元格进行操作，在本例中为列G。

相关内容