• 遍历所有工作表，按SpecialCells查找包含公式的单元格

Sub UpdateIndexFormulasWithFilePathInB2()
    Dim filePath As String
    Dim sFormula As String
    Dim oSht As Worksheet
    Dim c As Range, formRng As Range
    Const oldFILEPATH = "[OldFilePath]"
    ' Modify sheet name as needed to get the new file path
    filePath = Sheets("Sheet1").Range("B2").Value
    ' Loop through sheets
    For Each oSht In ThisWorkbook.Sheets
        ' Get all cells with formula
        Set formRng = oSht.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
        If Not formRng Is Nothing Then
            For Each c In formRng.Cells
                sFormula = c.Formula2
                If VBA.InStr(1, sFormula, "Index", vbTextCompare) > 0 Then
                    c.Formula2 = Replace(sFormula, oldFILEPATH, filePath)
                End If
            Next
        End If
    Next
End Sub

字符串

• Microsoft文档：*

Range.SpecialCells method (Excel)
如果您期望的结果是替换所有公式的FilePath，无论它们是否包含Index，代码将更简单。

Option Explicit
Sub Demo()
    Dim filePath As String
    Dim oSht As Worksheet
    Const oldFILEPATH = "[OldFilePath]"
    ' Modify sheet name as needed to get the new file path
    filePath = Sheets("Sheet1").Range("B2").Value
    ' Loop through sheets
    For Each oSht In ThisWorkbook.Sheets
        oSht.Cells.Replace What:=oldFILEPATH, Replacement:=filePath, _
            LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
            , FormulaVersion:=xlReplaceFormula2
    Next
End Sub

型

• Microsoft文档：*

Range.Replace method (Excel)的

谢谢你的回答，但我最终解决了使用VLOOKUP，HLOOKUP和我的宏的组合。
我的最后一个代码的问题是，为了更新直接引用外部文件的单元格与索引公式，Excel将创建一个弹出提示，以选择每个单元格与所述公式的引用文件。我希望宏将能够绕过提示完全或至少减少到只做一次。
我的解决方法基本上是用一个公式填充单元格，该公式具有锁定的引用，该引用具有宏将公式复制到合适的列，以便刷新外部工作表中的值，这将我的外部文件引用弹出窗口减少到只发生一次。