All right?
I believe the code below can help.
I did based on the spreadsheet that you showed above.
Basically, it sums the values of column A:A according to the interval stipulated in cell "E3". After the sum is made, the division is made by the number in the stipulated interval (ie calculates a simple average).
This calculation is made up to the last row filled, but if the range exceeds the list of values and empty cells are selected in the calculation range, the filled cells will be considered as zero, which affects the calculation of the average.
The spreadsheet I used for testing looked like this:
And the code is this:
Private Sub btCalcular_Click()
Dim W As Worksheet
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim UltCel As Range
Dim Intervalo As Integer
Dim Soma As Double
Application.ScreenUpdating = False
'Declara e seleciona a aba em que serão feitos os cálculos
Set W = Planilha1
W.Select
'Encontra a última linha preenchida
Set UltCel = W.Cells(W.Rows.Count, 1).End(xlUp)
'Recebe o intervalo de células para cálculo das médias
Intervalo = W.Range("E3").Value
A = 2
B = 2
C = 2
Soma = 0
'Apaga as médias preenchidas antes
W.Range("E1:XFD1").ClearContents
W.Range("E1").Select
'Calcula as médias até a última linha preenchida e insere os valores nas células do campo "Médias"
Do While A <= UltCel.Row
Do While B <= Intervalo + 1
Soma = Soma + W.Range("A" & C).Value
C = C + 1
B = B + 1
Loop
ActiveCell.Value = (Soma / Intervalo)
A = A + Intervalo
'Intervalo = Intervalo + Intervalo
B = 2
C = A
Soma = 0
'Pula uma célula para o lado para incluir a próxima média
ActiveCell.Offset(0, 1).Select
Loop
'Apaga as variáveis
Set W = Nothing
Set UltCel = Nothing
A = Empty
Intervalo = Empty
Soma = Empty
Application.ScreenUpdating = True
MsgBox "Feito!"
End Sub
Anything, just call here!
In this case, all you need to do is fill out an automatic after this.
– Marcus Felipy Glatz Rodrigues