VBA 宏，用于更新包含引用外部工作表的 INDEX 函数的单元格-解网

问：

我是 VBA 宏的新手，因此任何帮助都表示赞赏。我正在尝试创建一个 VBA 宏，该宏可以更新包含直接引用外部工作表的 INDEX 函数的多个单元格。我目前没有使用与此相关的数据连接。

我目前的进度是这样的：

Sub UpdateIndexFormulasWithFilePathInB2()
    Dim i As Integer
    Dim filePath As String
    Dim formula As String

    filePath = Range("B2").Value

    For i = 4 To 14
        ' Get the current formula from the cell
        formula = Cells(i, 2).formula

        ' Update the formula to use the file path from cell B2
        formula = Replace(formula, "[OldFilePath]", filePath)

        ' Set the updated formula back in the cell
        Cells(i, 2).formula = formula
    Next i
End Sub

我已经成功地遍历了单元格，但我发现我需要找到并选择包含INDEX函数的每个单元格的参考工作表。有没有办法让我通过让宏选择文件本身来完全绕过这一点，或者将选择限制为仅一次以使用该函数更新所有单元格，因为所有单元格都引用相同的引用？

VBA Excel-公式

只是一个建议，您也可以使用功能区上的查找和替换来完成此操作。记下外部链接的单元格中显示的语法（类似于）。然后对新文件路径使用相同的格式，包括方括号，并全部替换。这也可以在VBA中使用该方法完成。但是，如果您有理由只针对公式中的链接，那么这也许不能作为一揽子修复。C:\Users\User\Desktop\[TEST.xlsx]Range.ReplaceINDEX()

答：

0赞 taller 10/31/2023 #1

遍历所有工作表，并通过以下方式找到带有公式的单元格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 方法（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 方法（Excel）

0赞 R.T 11/2/2023 #2

感谢您的回答，但我最终选择了 VLOOKUP、HLOOKUP 和我的宏的组合。

我上一个代码的问题是，为了使用直接引用外部文件的 INDEX 公式更新单元格，Excel 将创建一个弹出提示，以便为具有所述公式的每个单元格选择引用文件。我希望宏能够完全绕过提示，或者至少减少到只做一次。

我的解决方法基本上是用带有锁定引用的公式填充单元格，这些公式具有宏将公式复制到合适的列，以便刷新外部工作表中的值，这将我的外部文件引用弹出窗口减少到只发生一次。

VBA 宏，用于更新包含引用外部工作表的 INDEX 函数的单元格

VBA Macro for updating cells containing INDEX functions that reference an external worksheet

评论

评论