查找和替换单元格中的值-解网

问：

我有一个产品代码电子表格。我需要改变其中的大约 200 个。

下面的代码删除了另一张工作表上单元格中的所有数据，而不是将其替换为第二列中的新产品代码。

Sub Multi_FindReplace()

    Dim sht As Worksheet
    Dim fndList As Integer
    Dim rplcList As Integer
    Dim tbl As ListObject
    Dim myArray As Variant

    Set tbl = Worksheets("Sheet4").ListObjects("Table1")

    Set TempArray = tbl.DataBodyRange
    myArray = Application.Transpose(TempArray)
  
    fndList = 1
    rplcList = 2

    For x = LBound(myArray, 1) To UBound(myArray, 2)
        For Each sht In ActiveWorkbook.Worksheets
            If sht.Name <> tbl.Parent.Name Then
          
                sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
                  LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                  SearchFormat:=False, ReplaceFormat:=False
        
            End If
        Next sht
    Next x

End Sub

Excel VBA

Sub Multi_FindReplace()

    Dim sht As Worksheet, wb As Workbook, x As Long
    Dim fnd As String, rplc As String, tbl As ListObject, data As Variant

    Set wb = ActiveWorkbook
    Set tbl = wb.Worksheets("Sheet4").ListObjects("Table1")
    data = tbl.DataBodyRange.Value 'no need to transpose
    
    For x = LBound(data, 1) To UBound(data, 1) 'loop over data rows
        fnd = data(x, 1)                       'find value
        rplc = data(x, 2)                      'replace value
        If Len(fnd) > 0 And Len(rplc) > 0 Then 'make sure there are a pair of values
            For Each sht In wb.Worksheets
                If sht.Name <> tbl.Parent.Name Then
                  
                  sht.Cells.Replace What:=fnd, Replacement:=rplc, _
                      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                      SearchFormat:=False, ReplaceFormat:=False
                
                End If
            Next sht
        End If           'have a pair of values
    Next x

End Sub

查找和替换单元格中的值

Find and Replace values in cells

评论

评论