EXCELのVBAで相対参照の数式をクリップボードを使用せずコピーする方法

R1C1形式の数式を使うのがミソ

Sub 数式のコピー()
    Dim i As Long
    Dim cell As Range
    For i = 2 To 10
        Set cell = Cells(i, "A")
        cell.Formula = Application.ConvertFormula("=A1+B1", xlA1, xlR1C1, , Range("A2"))
        ' または
'        cell.FormulaR1C1 = "=R[-1]C+R[-1]C[1]"
        
        '値に変換
'        cell.Value = cell.Value
    Next
End Sub

実行前

実行後

---

備忘録

複数セルの一括設定も可能

Sub 数式のコピー()
    Dim i As Long
    Dim rng As Range
    For i = 2 To 10
        Set rng = Cells(i, "A").Resize(1, 2)
        rng.Formula = Array(Application.ConvertFormula("=A1+B1", xlA1, xlR1C1, , Range("A2")), _
                            i * 2)
        ' または
'        rng.FormulaR1C1 = Array("=R[-1]C+R[-1]C[1]", i * 2)
        
        '値に変換
'        rng.Value = rng.Value
    Next
End Sub