Repeating a loop

[vicktor schausberger]

Hello everybody
I have a problem trying to repeat a code, I avoid to use any function or formula because I want to see how the loop really work
I need the code is loaded here to go to the lastrow and the result on the place is shows in the workbook.

 

[AlanSidman]

Did not see any code in your SS. Also, you have not explained what you wish to happen here. Please explain as your spreadsheet is not self explanatory. I have no idea what you wish to happen.

 

[vicktor schausberger]

sorry about that.

Sub ar2()
      Dim i As Long, a As Long, b As Long
      Dim co As Long, ae As Integer
         For colno = 2 To 7
                     a = 0
                                 For i = 2 To 13
                                             a = a + Cells(i, colno).Value
                                 Next i
                    b = 0
                                 For co = 2 To Cells(Rows.count, colno).End(xlUp).Row
                                            b = b + 1
                                Next co
                     Range(Cells(3, 8 + colno), Cells(3, 8 + colno)).Value = b '###COUNT
                     Cells(2, 8 + colno).Value = a  '####SUM
                     ae = a / b
                     Range(Cells(4, 8 + colno), Cells(4, 8 + colno)).Value = ae '###AVERAGE
            Next colno
End Sub

I make a mistake. in this workbook you will see on ("K35") the results from this code,
this code add the columns, count the values, and divide the total by the count, yes I understand this is average, But I don't want to use any formula, application or function, I am trying to learn VBA by myself and now reading about loops, I found out there are not theory how to repeat the whole code.
I mean in this workbook you will see the J810 empty, because my code only work one time, so what I respectfully request is how to repeat it to the end of the array.
and thank you so much for reading this.

 

[Hui]

It appears like you want to add up, count and average the numbers in each column and
Can you please explain what you are trying to achieve in J2 : P32 ?

 

[Hui]

I'd do something like:

Sub ar2()
Dim i As Long, a As Long, b As Long
Dim colno As Long, lr as Integer

For colno = 3 To 7
   a = 0
   b = 0
   lr = Cells(Rows.Count, colno).End(xlUp).Row
      
   For i = 2 To lr
      a = a + Cells(i, colno).Value
      If Cells(i, colno).Value <> "" Then b = b + 1
   Next i
   
   Cells(3, 9 + colno) = a  'SUM
   Cells(4, 9 + colno) = b 'COUNT
   Cells(5, 9 + colno) = a / b 'AVERAGE
Next colno
End Sub

 

[vicktor schausberger]

"Hui" ►Excel Ninja◄ Hi Sir.
Is a real honor for me to see You read my Post, Thank you so much.
I am looking to repeat the code, in my basic knowledge I was expecting just to add a new variable but do not work.
the snippet code fill the first set or square, but I need to fill the rest of the square I call them run 1 run 2 run 3 etc
Run 1 = (“B2:B13”) my code work for this but
RUN 2 = (“B3:B14”) do not work for this results
RUN 3 = (“B4:B15”) and do not work here
I would like make the code run to the last row on sets of 12 and every time just start from the next row or step 1 only.

Sub ar2()
      Dim i As Long, a As Long, b As Long
      Dim co As Long, ae As Integer
     
        
         For colno = 2 To 7
                     a = 0
                     b = 0
                                 For i = 2 To 13
                                             a = a + Cells(i, colno).Value
                                             b = b + 1
                                 Next i

                     Range(Cells(3, 8 + colno), Cells(3, 8 + colno)).Value = b '###COUNT
                     Cells(2, 8 + colno).Value = a  '####SUM
                     ae = a / b
                     Range(Cells(4, 8 + colno), Cells(4, 8 + colno)).Value = ae '###AVERAGE
                    
           Next colno
     
End Sub

run 1 B C D E F G
sum 156 234 312 390 468 546 this is ► a = a + Cells(i, colno).Value◄
count 12 12 12 12 12 12 this is ► For i = 2 To 13◄
average 13 20 26 32 39 46 this is ►ae = a / b◄
this SQUARE that I call run 1 are all I get from my code, so the question here is How can I add another FOR...NEXT in order to keep going filling the
results for run 2 run3 etc etc as long the code cover all the dynamic array 12 by 12

run 2 B C D E F G
sum
count
average

Thank you Mr. "Hui" for asking.

 

[Hui]

What causes the data to change ?

 

[vicktor schausberger]

Thank you Mr. Hui.
I assume you ask about the array, the array is just any kind of numbers like any random or series, my real purpose is to study the FOR ....NEXT structure, in most of the books or internet they just ofert something like:
for variable = number to something
"do something
next
in general, as simple data, so I am trying to in the place say ►"do something"◄ place a whole code
Thank you.

 

[jolivanes]

In a new workbook, put this code in a regular module, go back to a sheet and run this macro.
Also paste the code in H1 of that sheet so you can follow the happenings and have a look at the code also.
Keep your eyes on the left corner of your sheet (Cell A1 and area)

Sub See_What_Happens()
Dim i As Long, j As Long, x As Long
x = 0
    For i = 1 To 5
        For j = 1 To 6
            Cells(j, i).Value = x + 1
                x = x + 1
            MsgBox "Do you see what happened?"
        Next j
    Next i
End Sub

 

[vicktor schausberger]

Sorry :" jolivanes": for my delay. Thank you. Nice code but sorry do not accomplished my expectations

 

[jolivanes]

Is is just an example of looping.

 

[Marc L]

Hi !​

as long the code cover all the dynamic array 12 by 12

According to post #3 attachment you must paste this looping demonstration to the Sheet1 worksheet module​

​

edit v3 :​

Sub Demo0()
        Dim V(), A(), C%, R&, F&, W()
            V = [{"sum","count","average"}]
            Columns("I:R").Clear
    With Me.UsedRange.Columns
          ReDim A(3, .Count)
        For C = 1 To .Count:  A(0, C) = Split(.Cells(C).Address, "$")(1):  Next
        For R = 1 To 3:       A(R, 0) = V(R):                              Next
            V = .Value2
    End With
    For F = 1 To UBound(V) - 11
            W = A:  W(0, 0) = "#" & F
        For R = F To F + 11
        For C = 1 To C - 1
            If V(R, C) Then W(1, C) = W(1, C) + V(R, C): W(2, C) = W(2, C) + 1
        Next C, R
        For C = 1 To C - 1
            If W(2, C) Then W(3, C) = W(1, C) / W(2, C)
        Next
        With Cells(F + (F - 1) * 4, 10).Resize(4, C)
            .NumberFormat = "#,##0_W;;0_W;_y@"
            .Value2 = W
        End With
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Marc L]

Previous post updated for a VBA optimization …​

​

I avoid to use any function or formula because I want to see how the loop really work

As a reminder, it can be achieved with only an unique loop in a VBA procedure using the efficient formula way​

so you can easily learn « how the loop really works » !​

​

I found out there are not theory how to repeat the whole code

As it's explained in the VBA help, on Web, …​

​