VBA Looping formula

[montecarlo2012]

Hello, Fox, Hope you SAFE.

Formula.Average
The same formula must be entered in the
successive cells in a worksheet columns, but
One of the references, in the formula must be
incremente, by more than one row.

The spreadsheet contains 48 blocks of data,
in columns (“A:D”).
(“For illustration purpose I am showing 3 only”)

Each block is separated by four rows.

The average of each column
in the data block
Is to be calculated and entered
in the first 4 rows of columns (“F:I”).

Thus, the formula strings for rows 2 and 3 in column F must be
“=AVERAGE(A2:A6)” And
“AVERAGE(A11:A15)”, respectively
the reference to the
row index in
these formulas
increases by 9, but the
Difference in the cells that hold the formulas
is only 1.

Therefore,
the correct formula for cell F3
cannot be obtained with a
copy and paste of cell F2 Because the
difference between the
row indices in
successive formulas is a constant.

But when I tried
a Variable -> J <- for the outer loop
do not work.
And the real data is for the whole year
48 weeks (or blocks).

Sub Montecarlo_avr()

      Dim i As Integer
               For i = 0 To 2
                        Cells(i + 2, 6).Formula = "=Average(A" & (i + 2) + (i * 8) & ":A" & (i + 6) + (i * 8) & ")"
                        Cells(i + 2, 7).Formula = "=Average(B" & (i + 2) + (i * 8) & ":B" & (i + 6) + (i * 8) & ")"
                        Cells(i + 2, 8).Formula = "=Average(C" & (i + 2) + (i * 8) & ":C" & (i + 6) + (i * 8) & ")"
                        Cells(i + 2, 9).Formula = "=Average(D" & (i + 2) + (i * 8) & ":D" & (i + 6) + (i * 8) & ")"
               Next i

End Sub

 

[DanteAmor]

Try this:

Sub Montecarlo_avr()
  Dim a As Range
  For Each a In Range("A1", Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants).Areas
    With Range("F" & Rows.Count).End(3)(2).Resize(, 4)
      .Formula = "=AVERAGE(" & a.Address(0, 0) & ")"
      .Value = .Value
    End With
  Next
End Sub

If you want only the formulas, then remove this line from the macro:
.value = .value

Dante Amor
	A	B	C	D	E	F	G	H	I
1	Week 1					Average A	Average B	Average C	Average D
2	595	301	350	322		357	410.2	340.2	275.8
3	329	441	70	63		330.4	289.8	429.8	385
4	21	182	497	287		389.2	254.8	134.4	403.2
5	147	574	427	154
6	693	553	357	553
7
8
9
10	Week 2
11	385	434	567	84
12	399	322	637	315
13	427	147	203	546
14	126	329	203	532
15	315	217	539	448
16
17
18
19	Week 3
20	364	203	98	294
21	385	385	350	392
22	217	224	42	364
23	609	385	175	665
24	371	77	7	301
Hoja2

 

[montecarlo2012]

Thank you Sir. I click like, great job, really work. be safe

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.