Dynamic excel sum function

0
votes

I am trying to make a sum formula on a dynamic range. Much like in a pivot table.

Taking a look on the picture I want L15 to be the sum of the range from L16 to the blank row. As the range is dynamic I am not sure how to write it on my code. So far what I have is this:

If out.Range("A15").Cells(i, 1) = "Aktier" Then
        out.Range("L15").Cells(i, 1) = Application.WorksheetFunction.Sum(Range("L15").Cells(i + 1, 1), Range("L15").Cells(i + 1, 1).End(xlDown))
End If

So my question is basically how do I write something like Sum(A1:End(xlDown))? :)

Hope you can help me out guys! :D

Thanks in advance!

Data picture

2
excelvbadynamicsum
Try: out.Range("L15").Cells(i, 1).Value = Application.Sum(out.Range(out.Cells(i + 1, 12), out.Cells(i + 1, 12).End(xlDown))) - JvdV
Hi there Thanks for your quick reply however this only gives me the value "FALSE" after I've ran my sub. - Lelung
Hello mate. This just gives me a 0 value :( - Lelung
Please show more of your code. Are you looping over cells or are you only checking A15 for "Aktier". Would be helpfull to know what i variable holds and what it does for you. - JvdV
It is basically only this. After Aktier I have a line saying the same thing but using "Obligationer IG" and "UCITS alternative". i is just the row number for me so I am looping over different row numbers. - Lelung

2 Answers

0
votes

First I would recommend that every time that's possible, you get a structure more Data Base-like, so I would have a column repeating the concept and then you could use the Excel SUMIF function easily.

Probably that's not your case (that seems the output of an accounting program). Taking advantage of using VBA macros, you can use a loop to generate the column I mentioned before (you could do the sum as well using the concept, but I believe is cleaner generating a better data format). Please see the image below:Excel Sample to Try Macro

Sub Add_Concepts()

i = 1
Concept = Cells(i, 2)
i = 2
Do While (Cells(i, 2) <> "")
 Cells(i, 1) = Concept
 If Cells(i + 1, 2) = "" Then 'Change in concept
  Concept = Cells(i + 2, 2) 'New concept
  i = i + 2 'add 2 to skip the New concept line and the white space
 End If
  i = i + 1
Loop

End Sub

Now you can use the regular Excel functions.

Hope this helps!

0
votes

Skipping Steps

Snippet:

If out.Range("A15").Cells(i, 1) = "Aktier" Then
    With out.Range("L15")
        .Cells(i, 1) = Application.WorksheetFunction _
          .Sum(.Parent.Range(.Offset(1), .Offset(1).End(xlDown)))
    End With
End If

Working example:

Option Explicit

Sub SumToBlank()

    Dim out As Worksheet: Set out = ThisWorkbook.Worksheets("Sheet1")
    Dim i As Long: i = 1

    If out.Range("A15").Cells(i, 1) = "Aktier" Then
        With out.Range("L15")
            .Cells(i, 1) = Application.WorksheetFunction _
              .Sum(.Parent.Range(.Offset(1), .Offset.End(xlDown)))
        End With
    End If

End Sub