时间戳

首页 > 解决方案 > Sum of dynamic range

vba - Sum of dynamic range

问题描述

To begin with - I can't do that using functions because it is part of a script. Given dummy data of

28
53
32
24
29
25
93
80
20

My formula in Excel is =SUM($A$2:A2)/SUM($A$2:$A$10)

I tried to recreate it in VBA and I am stuck at the part of first parenthesis. Rest I think is fine

Sub testCumulPerc()
 
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim firstRow As Integer, lastRow As Integer
    Dim constSum As Integer
    Dim rng As Range
 
    Set wb = Application.ActiveWorkbook
    Set ws = wb.Sheets(1)
    firstRow = Range("A2").Row
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    constSum = Range("A2").Value
    
    For i = firstRow To lastRow
        Set rng = ws.Cells(i, 2)
            
       rng.Value = Application.Sum(constSum, ws.Cells(i, 1)) / Application.Sum(Range(Cells(firstRow, 1), Cells(lastRow, 1)))
    Next i
End Sub

It just sums me the 2 given values in first parenthesis, not the range. Also when it comes to the first iteration, it sums A2 with A2 and gives 56 which is not good. The only good calculated percentage is A3 row

Thank you in advance for your help

标签： vba

解决方案

只需根据循环的迭代次数调整开始范围的大小。使用Resize

像这样

sum(range("a2").resize(i-firstrow,1)

推荐阅读