Average of Changing Range Problem

[ianfrancis56]

Hi all,

I'm a little fried and I cannot figure out why this won't work, although I have a suspicion it has to do with the Range the Average function is being applied to.

Sub Returns()

Dim z@, y@, x@

With Sheets(1)
For z = 2 To 3
    For x = 2 To y = .Cells(2, z).End(xlDown).Row
        .Cells(x, z + 2) = .Application.Average( _
        .Range(.Cells(x, z).End(xlDown).Row))
    Next x
    Next z
End With

End Sub

Is Sheet 1 Columns B and C (starting in Row 2) are values. I want to look at the Average value of each column as it moves down (ie Average of values in cells B2 to the end, then with B3 being the first cell in the calculation, etc...). Appreciate any help, thanks!

 

[jim may]

Try:

Sub Returns()

Dim z@, y@, x@

With Sheets(1)
y = .Cells(2, 2).End(xlDown).Row
Range("D2").Select
For z = 2 To 3
    For x = 2 To y
        .Cells(x, z + 2).FormulaR1C1 = "=Average(R2C[-2]:RC[-2])"
    Next x
ActiveCell.Offset(, 1).Select
Next z
End With

End Sub