改进RegEx在单元格范围内多次替换的功能

Sub RegexReplaceInRange()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim regex As Object
    Dim pattern As String
    Dim replacement As String
    
    ' 设置工作表和范围
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称
    Set rng = ws.Range("A1:A10") ' 修改为你想要处理的单元格范围
    
    ' 设置正则表达式模式和替换文本
    pattern = "your_pattern_here" ' 替换为你的正则表达式模式
    replacement = "your_replacement_here" ' 替换为你的替换文本
    
    ' 创建正则表达式对象
    Set regex = CreateObject("VBScript.RegExp")
    regex.Global = True
    regex.IgnoreCase = True ' 设置为True以忽略大小写
    
    ' 遍历范围内的每个单元格并进行替换
    For Each cell In rng
        If cell.Value <> "" Then
            cell.Value = regex.Replace(cell.Value, replacement)
        End If
    Next cell
    
    MsgBox "替换完成！"
End Sub

pattern = "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b"
replacement = "[email protected]"