无需$符号，复制公式到单元格区域时避免引用自动递增

问题背景 #

需要给D105:D109区域的每个单元格写入引用D107的公式，但直接用Range.Formula赋值时，Excel会自动递增引用；用绝对引用$的话，后续复制区域时引用无法动态调整（比如复制到D110:D114时，希望引用D112，即新区域的中间行）。

最优解决方案：使用R1C1相对引用格式 #

利用Excel的R1C1引用格式，让每个单元格的引用相对于自身位置指向区域的中间行，既能满足当前区域统一引用D107的需求，后续复制区域时也能自动调整到新区域的对应位置。

代码示例 #

Dim Middle As Long, secondRow As Long, firstRow As Long
Middle = 107
firstRow = 105
secondRow = 109

With Worksheets("Model").Range("D" & firstRow & ":D" & secondRow)
    ' 计算每个单元格到目标行的相对偏移量，并用R1C1格式写入公式
    .FormulaR1C1 = "=R[" & (Middle - .Row) & "]C"
End With

效果说明 #

• 当前区域D105:D109的公式转成A1格式后，全部为=D107，符合预期。
• 若将该区域复制到D110:D114，每个单元格的公式会自动调整为引用D112（新区域的中间行），满足后续动态变化的需求。

备选方案：数组赋值+Value属性（仅适用于固定原引用的场景） #

如果后续复制不需要调整引用，仅需当前区域统一引用D107，可以修正原数组代码的维度问题，并用Value属性赋值避免Excel自动调整引用：

代码示例 #

Dim Middle As Long, ManagAreaLength As Long, secondRow As Long, firstRow As Long, i As Long
Middle = 107
firstRow = 105
secondRow = 109
ManagAreaLength = secondRow - firstRow + 1

Dim TmpArray() As Variant
ReDim TmpArray(1 To ManagAreaLength) ' 匹配区域的单元格数量

For i = LBound(TmpArray) To UBound(TmpArray)
    TmpArray(i) = "=D" & Middle
Next i

' 用Value属性直接写入文本，Excel会自动识别为公式且不调整引用
Worksheets("Model").Range("D" & firstRow & ":D" & secondRow).Value = TmpArray

注意 #

此方法写入的公式是固定引用D107，后续复制区域时，公式不会自动调整，仅适用于不需要动态变化的场景。

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