Пример макроса: Программная вставка на активный лист разных формул

Sub Formulas_Example()   '' e24
    'Assign a hard-coded formula to a single cell
    Range("b3").Formula = "=a1+a2"
    
    'Assign a flexible formula to a range of cells
    Range("d1:d30").FormulaR1C1 = "=RC6+RC7"

    'Reference D5 (Absolute)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"

    'Reference D6 (Relative)
    '=D6
    Range("a2").FormulaR1C1 = "=R[4]C[3]"

    'Reference D5 (Absolute Row, Relative Column)
    '=D$5
    Range("a3").FormulaR1C1 = "=R5C[3]"

    'Reference D8 (Relative Row, Absolute Column)
    '=$D8
    Range("a4").FormulaR1C1 = "=R[4]C4"
    
    ' Formula With Variable
    Dim colNum As Long
    colNum = 4
    Range("a5").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNum
    
    ' Define a String Variable Containing the Formula
    Dim strFormula As String
    Dim cell As Range
    Set cell = Range("F31")
    ' Storing string to a variable and assigning to "Formula" property
    strFormula = "=SUM(F1:F30)"
    cell.Formula = strFormula
        
    ' Using variables to build a string
    ' and assigning it to "Formula" property
    Dim fromRow As Long, toRow As Long
    fromRow = 1
    toRow = 30
    strFormula = "=SUM(G" & fromRow & ":G" & toRow & ")"
    Set cell = Range("G31")
    cell.Formula = strFormula
End Sub

(function() {
    var oSheet = Api.GetActiveSheet();

    // Assign a hard-coded formula to a single cell
    oSheet.GetRange("b3").Formula = "=a1+a2";

    // Assign a flexible formula to a range of cells
    var oRange = oSheet.GetRange("d1:d30")
    oRange.ForEach(function(cell) {
        cell.Formula = "=" + oSheet.GetCells(cell.GetRow(), cell.GetCol()+2).Address + "+" + oSheet.GetCells(cell.GetRow(), cell.GetCol()+3).Address;
    });

    // Reference D5 (Absolute)
    // =$D$5
    oSheet.GetRange("a1").Formula = "=" + oSheet.GetRangeByNumber(4, 3).Address;

    // Reference D6 (Relative)
    // =D6
    oSheet.GetRange("a2").Formula = "=" + oSheet.GetRangeByNumber(5, 3).GetAddress(false, false, "xlA1", false);

    // Reference D5 (Absolute Row, Relative Column)
    // =D$5
    oSheet.GetRange("a3").Formula = "=" + oSheet.GetRangeByNumber(4, 3).GetAddress(true, false, "xlA1", false);

    // Reference D5 (Relative Row, Absolute Column)
    // =$D8
    oSheet.GetRange("a4").Formula = "=" + oSheet.GetRangeByNumber(7, 3).GetAddress(false, true, "xlA1", false);
    
    // Formula With Variable
    var colNum = 3;
    oSheet.GetRange("a5").Formula = "=" + oSheet.GetRangeByNumber(0, colNum).Address + "+" + oSheet.GetRangeByNumber(1, colNum).Address;    

    // Define a String Variable Containing the Formula
    var cell = oSheet.GetRange("F31");
    // Storing string to a variable and assigning to "Formula" property
    var strFormula = "=SUM(F1:F30)";
    cell.Formula = strFormula;
    
    // Using variables to build a string
    // and assigning it to "Formula" property
    var fromRow = 1;
    var toRow = 30;
    strFormula = "=SUM(G" + fromRow + ":G" + toRow + ")";
    cell = oSheet.GetRange("G31");
    cell.Formula = strFormula;
})();