VBA continuous sum loop

Question

I am trying to create loop which is summing numbers in column N until certain value is reach then write something to cell M. I have this code working but what I actualy need is to after it found the set value to go to next row and start from 0 untill it finds again the value and then again go to next line and sum from 0 to some value.

Example of data

INV SIZE of INV
26530492    1
26530520    1
26530521    1
26530523    1
26530527    1
26530528    1
26531080    1
26531083    1
26531112    1
26531114    1
26543723    1
26543737    1
26556566    1
26556893    1

in first column are invoices and second column is showing size of the file. The loop would go throug Size column and sum rows until value is reached like 5, then it will continue until another value is reached and so on...it will also add condition to new column like first sum is number 1, second sum 2, etc..

also I am playing with code below

    Sub Sum_loop()

'~~> j stands for number of summed segment  
        j = 1
        dbSumTotal = 0
        lastrow = Range("N" & Rows.count).End(xlUp).Row
        For i = 2 To lastrow Step 1
'~~> in column N are numbers for sum
            dbSumTotal = dbSumTotal + Cells(i, "N")

            If (dbSumTotal >= 3 Or dbSumTotal <= 3) Then
            Cells(i, "O") = j
'~~> reset sum to 0 
            If dbSumTotal >= 3 Then 
               dbSumTotal = 0
'~~> for next sum raise the segment number
               j = j + 1
              End if
            End If
        Next i

    End Sub

@FilipOndo I suggest you add screen-shot of your data, and some manual example of what you are trying to achieve, I'm quite sure we will be able to offer a more reliable solution. — Shai Rado
– Shai Rado, Commented Apr 17, 2018 at 11:46
Also, put in your actual code - this is a small enough proc that you don't need an MVCE. If you're normally using .Value then use it. — FreeMan
– FreeMan, Commented Apr 17, 2018 at 11:47
Just a note: Don't ever use On Error Resume Next without error handling! This line just hides any errors but they still occur, you just cannot see them. Remove it, or implement a proper error handling instead. — Pᴇʜ
– Pᴇʜ, Commented Apr 17, 2018 at 11:57
Testing is the worst time to use OERN! It hides every error, pretending it doesn't exist. It should be used sparingly and only when you know exactly what you're doing and why you're doing it. If you permanently delete that line, you'll probably see exactly what's wrong with your code. — FreeMan
– FreeMan, Commented Apr 17, 2018 at 12:01
If (dbSumTotal >= 3 Or dbSumTotal <= 3) Then is ALWAYS true for any value of dbSumTotal it will always be less than, equal to or greater than 3. What are you really looking for? — FreeMan
– FreeMan, Commented Apr 17, 2018 at 12:06

Neil B · Accepted Answer · 2018-04-17 13:24:20Z

2

Your logic is messed up. The first if statement can never be true. Remove it completely and write the cell value after the counter is incremented.

Sub Sum_loop()

'~~> j stands for number of summed segment  
        j = 1
        dbSumTotal = 0
        lastrow = Range("N" & Rows.count).End(xlUp).Row
        For i = 2 To lastrow Step 1
'~~> in column N are numbers for sum
            dbSumTotal = dbSumTotal + Cells(i, "N")
'~~> write value to cell and reset sum to 0 
            If dbSumTotal >= 3 Then  
               dbSumTotal = 0
'~~> for next sum raise the segment number
               j = j + 1
            End If
            Cells(i, "O") = j                   
        Next i

    End Sub

answered Apr 17, 2018 at 13:24

Neil B

2,2341 gold badge16 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

VBA continuous sum loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related