Why does VBA For Next Loop calculation incorrect?{VBA}

Question

I am new to VBA - trying to do a simple calculation using VBA but not able to make it work properly. Any help will be highly appreciated. Basically I have a row, the result should be equal to the previous result + current according cell

This is what I mean

	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 been using for Next but wasn't able to generate the result I want to see, the first output is correct, but after the first calculation, all the ongoing calculation is 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

So this code was able to get me the right result for the first calculation always, but the consecutive result are always wrong. Anybody know what is the issue? Sorry this question maybe very basis, but I can't figure it out myself.... Thank you for helping

It seems that you don't need two for loops. replace `column2` with `column1 +1` — Horaciux, Feb 08 '21 at 20:53

score 2 · Accepted Answer · answered Feb 08 '21 at 21:00

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

score 0 · Answer 2 · answered Feb 08 '21 at 21:03

0

try this

for i = 2 to usedrange.columns.count
   if i =2 then 
      cells(2,i) = cells(2,i).offset(-1,0)   ' column a
   else
      cells(2,i)= cells(2,1).offset(0,-1)+ cells(1,i)
   end if
next i

answered Feb 08 '21 at 21:03

Tomasz

426
2
10

Why does VBA For Next Loop calculation incorrect?{VBA}

2 Answers2