CODEWITHSUNDEEP
arraysvbasum
seigna
seigna

Reputation: 301

Can't cumulate the sum of array elements with VBA

I'm trying to cumulate the sums of values in an excel column of 4 values dimension (4,1).

So, I constructed the code below. For the first row in a column on the side Result, it is supposed to hold the same value as in the original Array. But then, once it is greater than the first row, it is supposed to get the previous element of result (i-1) and add to it the current column element (i).

VBA is telling me that the subscript is out of range :/ and I cant figure out why... so I dont even know if my code does what I want. 

Sub CumulativeSum()
    Dim i As Integer
    Dim j As Integer
    Dim rColumn() As Variant
    Dim result() As Variant
    ReDim result(1 To 4)
    rColumn = Worksheets("Sheet1").Range("E1:E4").Value2

    For i = 1 To 4
        result(1) = rColumn(1, 1)
        For j = 2 To 3
            result(j) = rColumn(j, 1) + result(j - 1)
        Next j
   Next i
   Dim dest As Range
   Set dest = Worksheets("Sheet1").Range("F1")
   dest.Resize(4, 1).Value = result
End Sub

Upvotes: 0

Views: 4439

Answers (2)

AJY
AJY

Reputation: 188

Don't have enough rep to add a comment but.. the reason why you're getting an error is because the Syntax for Cells is Cells([Row],[Column]). You're typing it in as Cells([Column],[Row]).

Try Range(Cells(1, 5), Cells(4, 5)) instead.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166381

Sub CumulativeSum()

    Dim dest As Range
    Dim i As Integer
    Dim j As Integer
    Dim rColumn() As Variant
    Dim result() As Variant
    ReDim result(1 To 4)
    rColumn = Worksheets("Sheet1").Range("E1:E4").Value2

    result(1) = rColumn(1, 1)
    For j = 2 To 4
        result(j) = rColumn(j, 1) + result(j - 1)
    Next j

    Set dest = Worksheets("Sheet1").Range("F1")
    dest.Resize(4, 1).Value = Application.Transpose(result)

End Sub

Upvotes: 2

Related Questions