Excel宏更新后LONG SHORT表公式单元格区域自动偏移，需锁定范围

问题说明 #

• LONG SHORT工作表的L、N、O列数据引用自ALL DATA工作表
• 运行下方的宏更新ALL DATA工作表数据后，LONG SHORT中的公式引用区域会自动偏移，需要锁定该引用范围

更新数据的宏代码 #

Sub datacopy()
    Sheets("All Data").Activate
    'lastrow = Sheets("All Data").Range("C55555").End(xlUp).Row
    Sheets("All Data").Range("C4").Select
    ActiveCell.EntireRow.Resize(11).Insert Shift:=xlDown
    
    Sheets("DERIVATIVES OI").Range("A2:O7").Copy
    Sheets("All Data").Range("c4:q9").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Sheets("DASHBOARD").Activate
    
    MsgBox "DATA UPDATED SUCCESSFULLY !"
End Sub

相关界面说明 #

截图展示了LONG SHORT工作表的内容，其中L、N、O列包含引用ALL DATA工作表的公式，当前引用为相对模式。

解决方法 #

要锁定引用范围，只需将公式中的相对引用改为完全绝对引用，具体操作：

1. 选中LONG SHORT工作表L、N、O列的公式单元格
2. 在公式编辑栏中选中引用的ALL DATA区域，按F4键切换为绝对引用（例如把All Data!C4改为All Data!$C$4，区域引用All Data!C4:C10改为All Data!$C$4:$C$10）
3. 批量处理：按Ctrl+H打开查找替换，查找对应列的引用前缀（如All Data!C），替换为带绝对引用符号的前缀（如All Data!$C$），点击「全部替换」即可批量修改

内容的提问来源于stack exchange，提问作者GAURAV SEMWL