vba вставка формул

vba-Вставка формул.vba

Public Sub testFormula()

'0 - строка с которой начинается пункт
'1 - строка на которой заканчивается пункт
'2 - столбец для формулы
Dim arr
arr = Array(Array(3, 11, 263), Array(13, 27, 264), _
            Array(29, 41, 265), Array(43, 47, 266), _
            Array(49, 54, 267), Array(56, 71, 268), _
            Array(73, 88, 269), Array(90, 94, 270), _
            Array(96, 103, 271), Array(105, 124, 272), _
            Array(119, 124, 273), Array(126, 137, 274), _
            Array(139, 147, 275), Array(149, 156, 276), _
            Array(158, 165, 277), Array(167, 174, 278), _
            Array(176, 186, 279), Array(188, 195, 280), _
            Array(197, 205, 281), Array(207, 212, 282), _
            Array(214, 222, 283), Array(224, 232, 284), _
            Array(234, 242, 285), Array(244, 251, 286), _
            Array(253, 260, 287) _
)

Dim firstColumn As String: firstColumn = "D"
Dim secondColumn As String: secondColumn = "C"
Dim resultColumn As Integer: resultColumn = 3

Dim resultRowSumFromAllCells As Integer: resultRowSumFromAllCells = 289

'"=((D4*C4+D5*C5+D6*C6+D7*C7+D8*C8)*100)/2*(СУММ(D4:D8))/100"

Dim globalSumColumn As String: globalSumColumn = ""
Dim globalSumSum As String: globalSumColumn = ""

Dim i
For i = LBound(arr) To UBound(arr)
    Dim arrRow: arrRow = arr(i)
    Dim startRow As Integer: startRow = arrRow(0)
    Dim endRow As Integer: endRow = arrRow(1)
    Dim resultRow As Integer: resultRow = arrRow(2)
    
    Dim stringPreResultFormula  As String: stringPreResultFormula = ""

    For counterFormulaRow = startRow To endRow
        stringPreResultFormula = _
            stringPreResultFormula & _
            firstColumn & _
            CStr(counterFormulaRow) & _
            "*" & _
            secondColumn & _
            CStr(counterFormulaRow) & _
            "+"
    Next counterFormulaRow
    Debug.Print stringPreResultFormula
    
    globalSumColumn = globalSumColumn & stringPreResultFormula
    
    stringPreResultFormula = Left(stringPreResultFormula, Len(stringPreResultFormula) - 1)
    Debug.Print stringPreResultFormula
    
    Dim finalFormula As String
    Dim sumData As String: sumData = "SUM(D" & startRow & ":D" & endRow & ")"
    
    globalSumSum = globalSumSum & sumData & "+"
    
    finalFormula = "=((" & stringPreResultFormula & ")*100)/2*(" & sumData & ")/100"
    Debug.Print finalFormula
    
    Cells(resultRow, resultColumn).Formula = finalFormula
Next i

globalSumColumn = Left(globalSumColumn, Len(globalSumColumn) - 1)
globalSumSum = Left(globalSumSum, Len(globalSumSum) - 1)

Dim formulaResultRow As String
formulaResultRow = "=((" & globalSumColumn & ")*100)/2*(" & globalSumSum & ")/100"
Debug.Print formulaResultRow

Cells(resultRowSumFromAllCells, resultColumn).Formula = formulaResultRow

End Sub