Formula.array in excel macros
First, =average() doesn't need to be array entered:
Option Explicit
Sub testingaverage()
Dim myRng As Range
Dim rng As Range
Dim LastRow As Long
Dim i As Long
With ActiveSheet
Set rng = .UsedRange
LastRow = rng.Rows(rng.Rows.Count).Row
For i = 1 To LastRow Step 100
Set myRng = .Cells(i, "F").Resize(100, 1)
.Cells(i, "L").Formula _
= "=Average(" & myRng.Address(external:=True) & ")"
Next i
End With
End Sub
Chinmaybl wrote:
Hi Guys I am trying to use this macro ..
Sub testingaverage()
Set rng = ActiveSheet.UsedRange
lastRow = rng.Rows(rng.Rows.Count).Row
For i = 1 To lastRow Step 100
Cells(i, "L").FormulaArray = "=Average(f1:f100)"
Next
End Sub
This macro gives me average for F1:F100 in every 100th row of column
L.
What I want to do is :
----the first time I get average it should be F1:F100
----second time It should be F101:F200
---Third time F201:F300 ....... and so on .....
So basically my question is how do I change the x value in Fx
Any help would be great
Thanks
Chinmay
--
Dave Peterson
|