try a simple for...next loop

Sub test()

Dim rw As Long, maxrow As Long
Dim Inc As Long ' offset for formula
Dim Col As String ' column for result

Inc = 10 ' used for our offset
Col = "C"
maxrow = Range("a1").End(xlDown).Row
For rw = 1 To maxrow / Inc
Cells(rw, Col).Formula = "=Average(A" & (rw - 1) * Inc
+ 1 & _
":A" & rw * Inc & ")"
Next

End Sub



Patrick Molloy
Microsoft Excel MVP




-----Original Message-----
Hello,
I have 3 columns of data and 60 rows. I want to
average 10 rows
at a time, eg avg(a1:a10) and next formulat will be from
avg(a11:a20),
etc. How do i auto increment the cells? when i copy the
formula the
average function increment by one only from avg(a1:a10)
to avg
(a2:a11). I want it to go from avg(a1:a10) to avg
(a11:a20).. Any
help? thanks.
.

Patrick,

Thanks for all your help.


"Patrick Molloy" wrote in message ...
try a simple for...next loop

Sub test()

Dim rw As Long, maxrow As Long
Dim Inc As Long ' offset for formula
Dim Col As String ' column for result

Inc = 10 ' used for our offset
Col = "C"
maxrow = Range("a1").End(xlDown).Row
For rw = 1 To maxrow / Inc
Cells(rw, Col).Formula = "=Average(A" & (rw - 1) * Inc
+ 1 & _
":A" & rw * Inc & ")"
Next

End Sub



Patrick Molloy
Microsoft Excel MVP




-----Original Message-----
Hello,
I have 3 columns of data and 60 rows. I want to
average 10 rows
at a time, eg avg(a1:a10) and next formulat will be from
avg(a11:a20),
etc. How do i auto increment the cells? when i copy the
formula the
average function increment by one only from avg(a1:a10)
to avg
(a2:a11). I want it to go from avg(a1:a10) to avg
(a11:a20).. Any
help? thanks.
.