Excel VBA sum with dynamic range

Question

I'm trying to create a excel macro calculating a sum of values in column A, using a certain dynamic range.

Basic excel example screenshot included below:

Excel snapshot

The final goal is to loop through the cells in column B till a "0" is reached. When a "0" is reached, in column A of that same row, a sum of all the values in column A is calculated until a new "0" in column B is reached. So in this basic example A1 should contain =sum(A2:A7) and A8 should contain the sum of the values A9 through A14.

My progress so far included below, still pretty new at all the VBA stuff.

Sub sumtill0()
'
' sumtill0 Macro
'

'
Cells(ActiveCell.Row, 1).Select
If Sheets("Blad1").Cells(2, 1).Value = nil And Sheets("Blad1").Cells(2, 1).Value <> "0" Then
    Sheets("Blad1").Activate
    Cells(2, 1).Range("A1").Select
       Else
    Dim artTekst As String
    If Sheets("Blad1").Cells(1, 2).Value = "0" Then
        Sheets("Blad1").Cells(1, 1).Fomula = "=SUM()"
    Else
        If Sheets("Blad1").Cells(1, 2).End(xlDown).Value = "0" Then
            Sheets("Blad1").Cells(1, 1).End(xlDown).Offset(1, 0).Value = "0"
            Sheets("Blad1").Cells(1, 1).End(xlDown).Fomula = "=SUM(???)"
            ActiveCell.Value = Sheets("Blad1").Cells(1, 2).End(xlDown).Offset(0, -2).Value
    End If

End If

End If

End Sub

Thanks :)

What exactly is your problem? Does the code not work? Is it always the case that A is only blank when B is zero? — SJR
– SJR, Commented Dec 5, 2017 at 15:26
The code isn't ready because I don't succeed at finding the write code to define the range of the sum. A could be blank even if B isn't zero. — Tefalpan
– Tefalpan, Commented Dec 5, 2017 at 15:30
The worksheet containing these values is created, using a macro, so I hoped to include the automatic sum. The macro's purpose is also used to find all the zero values in column B, if just using a fomula, I could manually select the range at all the zero's. That's no problem ofcourse, but with an excel file with over 2k rows, would be a little bit time consuming :) — Tefalpan
– Tefalpan, Commented Dec 5, 2017 at 15:38

SJR · Accepted Answer · 2017-12-05 16:50:01Z

1

Think this does what you want. It uses the Find method to find successive zeros and sums the range between each pair. If it cycles back to the start value, it sums to the bottom value (not sure what should happen if the first value in B is not zero).

Sub x()

Dim rFind As Range, s As String, r1 As Range

With Range("B1", Range("B" & Rows.Count).End(xlUp))
    Set rFind = .Find(What:=0, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
                      Lookat:=xlWhole, SearchDirection:=xlNext, searchFormat:=False)
    If Not rFind Is Nothing Then
        s = rFind.Address
        Do
            Set r1 = rFind
            Set rFind = .FindNext(rFind)
            If rFind.Address = s Then
                Set rFind = .Cells(.Cells.Count)
                r1.Offset(, -1).Value = Application.Sum(Range(r1.Offset(1, -1), rFind.Offset(, -1)))
                Exit Sub
            End If
            r1.Offset(, -1).Value = Application.Sum(Range(r1.Offset(1, -1), rFind.Offset(-1, -1)))
        Loop While rFind.Address <> s
    End If
End With

End Sub

edited Dec 5, 2017 at 16:50

answered Dec 5, 2017 at 15:48

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tefalpan Over a year ago

Thanks! That works like a charm, only small thing is it only calculates the sum if there's a zero at the end, so the sum corresponding to the last zero results in a 'blank sum'. This is solved by just adding a zero to the end of the file, is it also possible to include the last sum in the code? The first value is always zero so that's no problem :)

SJR Over a year ago

What should the sum be though if the last value is zero?

Tefalpan Over a year ago

The sum of all values until the end is reached

SJR Over a year ago

But the range being summed starts after the zero. Are you saying values continue in A after the last cell in B, or am I missing something?

Tefalpan Over a year ago

If the last 3 rows par exemple contain | |0| |5|3| |6|1| The result gives |5|0| |5|3| |6|1| (instead of 11)

|

Collectives™ on Stack Overflow

Excel VBA sum with dynamic range

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related