excel 查找和替换单元格中的值

owfi6suc 于 2023-01-31 发布在其他

关注(0)|答案(1)|浏览(302)

我有一张产品代码的电子表格。我需要更改其中的大约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

来源：https://stackoverflow.com/questions/74279459/find-and-replace-values-in-cells

1条答案

按热度按时间

m2xkgtsf1#

试试这个：

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

赞(0）回复(0）举报 2023-01-31

我来回答

excel 查找和替换单元格中的值

1条答案

相关问题

热门标签

最新问答