Excel：在列中搜索另一个工作表上的表中的单词，如果找到任何表，则从当前单元格中的单元格中插入值

1 回答

• 0

  在所有工作表中替换范围中定义的所有表达式的示例：

  Sub ReplaceMulti()
    Dim dictionary As Range, sh As Worksheet, data(), r&, search$, replace$
  
    ' Loads a translation table from a worksheet
    ' The first column is the searched string and the second is the replacement
    Set dictionary = [Sheet1!A1:B10]
    data = dictionary.value
  
    ' iterate each sheet
    For Each sh In ActiveWorkbook.Worksheets
  
      ' skip the sheet with the dictionary
      If sh.CodeName <> dictionary.Worksheet.CodeName Then
  
        ' iterate each expression
        For r = 1 To UBound(data)
  
          If Not IsEmpty(data(r, 1)) Then
            search = data(r, 1)
            replace = data(r, 2)
  
            ' replace in the sheet
            sh.Cells.replace What:=search, replacement:=replace, _
              LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
              SearchFormat:=False, ReplaceFormat:=False
  
          End If
        Next
      End If
    Next
  End Sub
  

  回复于 2024-04-24T05:02:30+08:00