如何批量替换Excel公式中的!Ref#为NewSheet？常规替换无效

方法1：通过Excel查找替换高级设置解决 #

这是最快捷的方式，核心是调整查找范围为「公式」：

• 按Ctrl+H打开「查找和替换」对话框
• 在「查找内容」输入 #REF!，「替换为」输入 NewSheet!
• 点击「选项」展开设置，将「查找范围」改为公式（默认是「值」，所以常规替换无法定位公式内的引用文本）
• 点击「全部替换」，确认操作即可完成批量替换

方法2：用VBA宏批量处理 #

如果查找替换遇到特殊情况（比如部分公式格式异常），可以用VBA实现精准替换：

• 按Alt+F11打开VBA编辑器
• 右键点击工作簿名称 → 插入 → 模块，粘贴以下代码：

Sub ReplaceREFWithNewSheet()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            On Error Resume Next
            Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
            On Error GoTo 0
            
            If Not rng Is Nothing Then
                For Each cell In rng
                    cell.Formula = Replace(cell.Formula, "#REF!", "NewSheet!")
                Next cell
            End If
        End If
    Next ws
    
    MsgBox "替换完成！", vbInformation
End Sub

• 按F5运行宏，或回到Excel界面通过「开发工具」→「宏」执行该程序

注意事项 #

• 操作前务必备份文件，防止数据意外丢失
• 若你的公式中错误引用的文本是!Ref#!而非标准的#REF!，请将代码和查找内容中的#REF!替换为!Ref#!
• 保存含宏的文件需选择.xlsm格式

内容的提问来源于stack exchange，提问作者Omar Gonzales