PDA

View Full Version : Question regarding loops sum and average



aqualiary
03-14-2014, 11:42 PM
Hi I'm pretty much a beginner in VBA currently learning it for uni, in one of our homeworks we were asked to use loops to find the average of the returns. I've been stuck on this for a really long time.

This is the code I use


Sub moreloopsave()
For j = 1 To 3
sum_ret = 0
For i = 1 To 6493
sum_ret = sum_ret + Range("B11").Cells(i, j)
Range("G11").Cells(i, 1) = sum_ret / 3
Next i
Next j
End Sub

and this is my results (which does not link to the results obtained with excel) and also I noticed, the results I've gotten with VBA just keeps increasing.

11400

Bob Phillips
03-15-2014, 03:55 AM
Your problem seems to be that you are setting the bounds of your loops incorrectly.

You are using j to control the loop of the columns. The amount columns are B:D, which in numeric terms is 2:4, but you are looping 1 to 3.

Similarity, when you do Range("B11").Cells(i, 1) that is pointing at B12 when i = 1.

And your loops are in the wrong order. Yoiu should be summing everything across, then next row, not everything down, and next column.

You can also dynamically find the last row. I would write the code more like


Sub moreloopsave()
Dim lastrow As Long
Dim sum_ret As Double
Dim i As Long, j As Long

Application.ScreenUpdating = False

lastrow = Range("B10").End(xlDown).Row

For i = 11 To lastrow
sum_ret = 0
For j = 2 To 4
sum_ret = sum_ret + Cells(i, j).Value
Next j
Cells(i, "G").Value = sum_ret / 3
Next i

Application.ScreenUpdating = True
End Sub

The Applicaton.ScreenUpdating is just to stop the screen being repainted every time you write to it, which causes a lot of flickering, and slows the code down.

aqualiary
04-02-2014, 09:55 PM
Thanks a lot