Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ...!!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array transpose for dynamic data without macros | Excel Discussion (Misc queries) | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Excel Array Formula? | New Users to Excel | |||
Excel XP VBA Array formula | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |