VBA: Calculate average based on data from previous column, variable length

[zeno]

I'm trying to figure out how to proceed in calculating an average with following conditions:
In column B, I'm looking for the average of variable length, defined by a Time value (e.g. 5 from cell 'Time') meaning this would be the average of the 5 first numbers from column A. The average calculation will appear in column B in the last of that Time row, i.e. the 5th row, starting from the row in which column A starts.
Column A's first number does not start in row 2 though, but in a lower row, probably somewhere between rows 20 and 50.
Example:
Column A starts in row 21;
Time = 5;
Result: Average in column B in row 26 with average of 5 first values of column A.
How can I write VBA code for such a formula?
Thanks for your help.

 

[AlphaFrog]

When you say Column A starts in row 21 ( I understand it's variable), does that mean all the cells above are empty?

Does the "Time" cell have the integer 5 in it or does it have a time value (e.g. "00:05:00") that has to be converted to an integer 5 ?

 

[zeno]

Yes, indeed, all cells above are empty, with the exception of row 1 that has the title.
Regarding the 'Time' cell, no conversion is needed. That value can be taken as is. I have it defined as a list with values from which I can choose.
Thank you for your clarifying questions.

 

[AlphaFrog]

[color=darkblue]Sub[/color] Avg_Time()

    [color=darkblue]With[/color] Range("A1").End(xlDown)
        .Offset(Range("TIME").Value, 1) = Application.WorksheetFunction.Average(.Resize(Range("TIME").Value))
    [color=darkblue]End[/color] [color=darkblue]With[/color]

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[zeno]

Thank you for your code.
Not sure why, but I receive following error:
run-time error 1004: method 'range' of object '_global' failed

Would you know why that is?

 

[AlphaFrog]

Do you have a cell named TIME ? If yes, what sheet is it on and what cell is it?

 

[zeno]

When I hold the cursor over following part I receive indication that object is required:

Resize(Range("TIME").Value)

Could the run-time error message 1004 be related to that?

It can identify
Range("TIME").Value
and gives it value = 5

 

[AlphaFrog]

As a test, run this and tell me what cell it selects.

Sub test1()
Range("A1").End(xlDown).Select
End Sub

 

[zeno]

It selects the last cell of column A.

 

[zeno]

Resize(Range("TIME").Value)

The next error message for the above code is:
run-time error 424: object required
So the range "Time" is correctly identified.