Excel VBA宏公式中如何设置随数据行变化的动态绝对引用

需要通过公式生成占比（%）列，要求公式绝对引用对应列的合计值，实现每行数据除以该合计值得到占比结果。但宏应用于不同文件时，各文件数据总行数不固定，合计值所在行号并非固定值，若直接设置固定行列号的绝对引用会得到错误计算结果。

当前待适配的核心代码行为：

Selection.FormulaR1C1 = "=RC[-1]/R53C2"

该代码逻辑为将选区左侧单元格的值除以绝对引用的B53单元格，但实际场景中该列合计值可能位于第52、54、55行或其他行，固定写死的R53C2引用会指向错误单元格，需要实现可适配不同使用场景、随当前选区动态调整的绝对引用逻辑。

注：以下为非完整宏代码，存在冗余代码行，待功能调试正常后再做清理优化

Sub ExposureColumn()
'
    Range("C6").Select
    Selection.FormulaR1C1 = "Exposure %"
    Range("B6").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=RC[-1]/R53C2"
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillUp
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.End(xlDown).Select
    ActiveCell.Offset(-1, 0).Select
    Selection.ClearContents
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    ActiveCell.FormulaR1C1 = "Exposure %"
    Range("C7").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone

原有代码逻辑本身已经通过两次End(xlDown)从B6定位到了B列的合计值单元格，只是没有把动态获取到的合计行号存下来，才需要硬编码R53C2。只需要加1个行号变量，在定位到合计单元格时读取行号，再拼接到R1C1公式里即可，不需要改动原有其他操作逻辑：

• 在过程开头声明变量存储合计值所在行号
• 定位到B列合计单元格时，立刻读取当前激活单元格的行号赋值给变量
• 公式引用处把硬编码的行号53替换为变量拼接值

调整后的可直接运行代码段：

Sub ExposureColumn()
    ' 声明变量存储合计值所在行号
    Dim totalRow As Long
'
    Range("C6").Select
    Selection.FormulaR1C1 = "Exposure %"
    Range("B6").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ' 获取当前定位到的B列合计单元格的行号
    totalRow = ActiveCell.Row
    ActiveCell.Offset(0, 1).Select
    Application.CutCopyMode = False
    ' 替换硬编码行号为动态拼接值，保持B列绝对引用
    Selection.FormulaR1C1 = "=RC[-1]/R" & totalRow & "C2"
    ' 后续原有逻辑全部保留
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillUp
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.End(xlDown).Select
    ActiveCell.Offset(-1, 0).Select
    Selection.ClearContents
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    ActiveCell.FormulaR1C1 = "Exposure %"
    Range("C7").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
End Sub

该写法完全适配不同行数的文件：只要B列数据结构保持“从B6开始连续向下为明细数据，最末行为合计值”的规则，两次End(xlDown)就能准确定位到合计单元格，动态取到的行号永远指向正确的合计值位置，不会出现引用偏移错误。同时引用保持绝对列锁B列、绝对行锁合计行，后续填充公式时所有占比计算都会统一除以B列合计值，符合计算要求。

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