adding up row values in VBAProject

Question

Say I have EXCEL 1 column (100 rows) of integers with values between 1 and 10 (with lrow being the last row). With VB, I am trying to add the rows UP TO a given sum (ex:25) to FIND THE ROW when the given sum (or less if adding the last row will bring the sum to over 25) is achieved.

For i = 1 To lRow
       Do While sum < 25
            sum= sum+ Cells(i, 1).Value
            Cells(2, 5).Value = sum
        Next j
        i = j-1
    Loop
Next i

var j doesn't increment. It keeps adding the fist row. WHY?

note: I want to continue to add the rest of the rows starting with the end position (row) of the first iteration. The reason for why i=j-1 is there.

That code won't run because you have Next j with no corresponding For. Fix that first. Or post your real code. — Andy G
– Andy G, Commented Feb 23, 2018 at 15:46

Scott Craner · Accepted Answer · 2018-02-23 15:53:44Z

2

You do not need two loops:

For i = 1 to lRow
    Sum =Sum + ActiveSheet.Cells(i, 1).Value
    If Sum >= 25 then exit for
Next i
ActiveSheet.Cells(2, 5).Value = sum
Msgbox "Stopped at Row: " & i

to continue down the column and get a print out every time it exceeds 25:

For j = 1 To lrow
    For i = j To lrow
        Sum = Sum + ActiveSheet.Cells(i, 1).Value
        If Sum >= 25 Then Exit For
    Next i
    ActiveSheet.Cells(i, 5).Value = Sum 
    Sum = 0   
    j = i
Next j

edited Feb 23, 2018 at 15:53

answered Feb 23, 2018 at 15:47

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

Comments

DisplayName · Accepted Answer · 2018-02-23 15:56:23Z

0

I'd go like follows

For i = 1 To lRow
    sum = sum + Cells(i, 1).Value
    If sum <= 25 Then i25 = i
Next

so:

sum will store the total sum of the cells values
i25 will store the row at which the sum was 25, or less if the sum exceeds 25 at row i25+1

answered Feb 23, 2018 at 15:56

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Collectives™ on Stack Overflow

adding up row values in VBAProject

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related