Formula.array in excel macros
 

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

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

Formula.array in excel macros
 

On Mar 1, 11:24 pm, Dave Peterson wrote:
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

That was too good ...it works ...Thanks a million ...!!!!!