基于表值的Vba excel替换字符串

Sub ReplaceStringBasedOnTable()
    Dim ws As Worksheet
    Dim table As ListObject
    Dim searchRange As Range
    Dim replaceRange As Range
    Dim i As Long
    
    ' 设置工作表
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' 设置表格范围
    Set table = ws.ListObjects("Table1")
    
    ' 设置搜索范围和替换范围
    Set searchRange = table.ListColumns("Search").DataBodyRange
    Set replaceRange = table.ListColumns("Replace").DataBodyRange
    
    ' 循环遍历每个搜索范围的单元格
    For i = 1 To searchRange.Cells.Count
        ' 获取搜索值和替换值
        Dim searchValue As String
        Dim replaceValue As String
        searchValue = searchRange.Cells(i).Value
        replaceValue = replaceRange.Cells(i).Value
        
        ' 替换字符串
        ws.Cells.Replace What:=searchValue, Replacement:=replaceValue, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next i
End Sub