excelvba

For Next Loop to add cell value to previous result


I have a row, the result should be equal to the previous result + current according cell.

A B C D
(1)Row 1 1 2 3 4
(2)Result 1 (1+2) (1+2)+3 ((1+2)+3)+4

I've a For Next. The first output is correct, but after the first calculation, all the subsequent calculations are incorrect.

Sub DeltaAllocationToCRSSum()
    Range("A2").Value = 1  ' Set starting value for the first cell in the result roll  
    Dim Column1 As Integer  
    Dim Column2 As Integer  
    For Column1 = 1 to 4     
        For Column2 = 2 To 5   
            Cells(2,Column2) = Cells(2,Column1)+Cells(1,Column2) 'meaning B2 = A2 + B1 for the first calculation  
        Next Column2    
    Next Column1

Solution

  • You only need one for loop, try this

    Sub DeltaAllocationToCRSSum()
    Range("A2").Value = 1  ' Set starting value for the first cell in the result roll
    Dim Column1  As Integer
     
    For Column1 = 1 To 4
     
    Cells(2, Column1 + 1) = Cells(2, Column1) + Cells(1, Column1 + 1) 'meaning B2 = A2 + B1 for the first calculation
     
    Next Column1
    End Sub