Excel VBA替换使用字符串查找和数组值替换

Sub ReplaceString()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim searchText As String
    Dim replaceText As String
    
    ' 设置要查找和替换的文本
    searchText = "要查找的文本"
    replaceText = "要替换的文本"
    
    ' 设置要进行替换操作的工作表和范围
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("A1:A10") ' 替换范围为A1:A10
    
    ' 遍历范围中的每个单元格，查找并替换文本
    For Each cell In rng
        If InStr(1, cell.Value, searchText) > 0 Then
            cell.Value = Replace(cell.Value, searchText, replaceText)
        End If
    Next cell
End Sub

Sub ReplaceValues()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim searchValue As Variant
    Dim replaceValue As Variant
    
    ' 设置要查找和替换的值
    searchValue = Array(1, 2, 3) ' 要查找的值
    replaceValue = Array("A", "B", "C") ' 要替换的值
    
    ' 设置要进行替换操作的工作表和范围
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("A1:A10") ' 替换范围为A1:A10
    
    ' 遍历范围中的每个单元格，查找并替换值
    For Each cell In rng
        For i = LBound(searchValue) To UBound(searchValue)
            If cell.Value = searchValue(i) Then
                cell.Value = replaceValue(i)
                Exit For
            End If
        Next i
    Next cell
End Sub