VBA代码执行后单元格绝对引用范围异常变更求助

运行以下VBA代码时，清除指定行数据后插入的带绝对引用公式，多次执行后单元格中的绝对引用会发生变化。由于公式所在行并未被删除，无法定位引用变更原因，求技术分析。

Sub LineArchive_DD119()

    Dim TMLastDistRow
    Dim Answer As VbMsgBoxResult
    Dim LastRowInRange As Long, RowCounter As Long
    
    TMLastDistRow = Worksheets("Trailer Archives").Cells(Sheet13.Rows.Count, "B").End(xlUp).Row + 1
    LastRowInRange = Sheet11.Range("A:A").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row ' Returns a Row Number
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    If Sheets("Dock Door Status").Range("P3").Value = "F" Then
        With Sheets("Dock Door Status")
            .Range("P3").Value = "F"
            .Range("C3:R3").Copy
        End With
        
        With Sheets("Trailer Archives")
            .Range("B" & TMLastDistRow).PasteSpecial Paste:=xlPasteValues
        End With
        
    Else
        Exit Sub
    End If
    
    Answer = MsgBox("Are you sure you want to clear DD119?", vbYesNo + vbCritical + vbDefaultButton2, "Dock Door 119 Data")
    If Answer = vbYes Then

        For RowCounter = LastRowInRange To 1 Step -1 ' Count Backwards
            If Sheet11.Range("A" & RowCounter) = Sheet22.Range("C3") Then '   If Cell matches our 'Delete if' value then
                Sheet11.Rows(RowCounter).EntireRow.Delete '       Delete the row
            End If
        Next
        
        With Sheets("Dock Door Status")
            .Range("D3:Q3").ClearContents
            .Range("D3").Formula = "=IF(E3=""RME"",""LOTO"",IF(AND(E3="""",F3<>""""),""OPEN"",""CLOSED""))"
        End With
        
        With Sheets("Dock Door Status")
            .Range("F3").Formula = "=IFERROR(INDEX('SSP Data'!B$2:B$50,MATCH($C3,'SSP Data'!$A$2:$A$50,0)),"""")"
            '.Range("G3").Formula = "=IFERROR(INDEX('SSP Data'!C$2:C$50,MATCH($C3,'SSP Data'!$A$2:$A$50,0)),"""")"
            '.Range("H3").Formula = "=IFERROR(INDEX('SSP Data'!D$2:D$50,MATCH($C3,'SSP Data'!$A$2:$A$50,0)),"""")"
            .Range("I3").Formula = "=IFERROR(INDEX('SSP Data'!E$2:E$50,MATCH($C3,'SSP Data'!$A$2:$A$50,0)),"""")"
            .Range("J3").Formula = "=IFERROR(INDEX('SSP Data'!F$2:F$50,MATCH($C3,'SSP Data'!$A$2:$A$50,0)),"""")"
            .Range("K3").Formula = "=IFERROR(INDEX('SSP Data'!G$2:G$50,MATCH($C3,'SSP Data'!$A$2:$A$50,0)),"""")"
            .Range("L3").Formula = "=IFERROR(INDEX('SSP Data'!H$2:H$50,MATCH($C3,'SSP Data'!$A$2:$A$50,0)),"""")"
            .Range("M3").Formula = "=IFERROR(INDEX('SSP Data'!I$2:I$50,MATCH($C3,'SSP Data'!$A$2:$A$50,0)),"""")"
            .Range("Q3").Formula = "=IF(G3="""","""",IF(L3>G3,""Future"",""Current""))"
        End With
        
    Else
        Exit Sub
    End If
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

问题原因分析 #

核心原因是Excel的自动引用调整机制：

• 代码中循环删除Sheet11工作表的行，如果Sheet11恰好是公式中引用的SSP Data工作表，那么当删除该工作表内的行时，Excel会自动调整所有指向该工作表的公式引用——哪怕是带绝对行号的引用（如$2），只要被删除的行落在引用范围内，Excel就会更新引用以匹配数据的新位置。
• 例如：若删除SSP Data的第2行，原公式中的'SSP Data'!B$2:B$50会自动变为'SSP Data'!B$3:B$51，因为原第3行及以后的数据上移了一行，Excel会自动修正引用指向原数据区域。

修复方案 #

1. 确认工作表代号指向：在VBA编辑器的工程窗口中，检查Sheet11对应的实际工作表名称是否为SSP Data，这是问题的关键前提。
2. 避免引用自动调整：
   • 改用INDIRECT函数创建静态引用，将公式中的区域引用改为字符串形式，Excel不会自动调整这类引用。例如：

     .Range("F3").Formula = "=IFERROR(INDEX(INDIRECT(""'SSP Data'!B$2:B$50""),MATCH($C3,INDIRECT(""'SSP Data'!$A$2:$A$50""),0)),"""")"
     

   • 若无需物理删除行，可改用清除单元格内容或标记删除（如设置特定状态）的方式替代EntireRow.Delete，避免触发引用调整。
3. 优化代码可读性：将代码中的工作表代号（Sheet11、Sheet22、Sheet13）替换为明确的工作表名称（如Worksheets("SSP Data")），避免因代号指向变更引发混淆。
4. 移除冗余操作：删除If Sheets("Dock Door Status").Range("P3").Value = "F" Then块内的.Range("P3").Value = "F"，该操作无实际意义。

内容的提问来源于stack exchange，提问作者Iron Man