Difficulty with relative addressing (averaging a column)

[looknsee]

I'm trying to write a Visual Basic program. In a spreadsheet, I have a column of data, and I want to define a cell under the column that contains the average of the cells in the column. The trouble is that I don't know the exact position or length of the column.

I have no trouble selecting the cell under the column, using a construct like...
Cells( MyRow + 1, MyCol )
(where "MyRow" is the row number of the cell at the bottom of the column.

I thought to record a macro where I perform the actions for a specific column, and then I would generalize the resulting code to use my variables instead of any kind of hard coding. The line of code that is recorded is this...
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-41]C:R[-1]C)"

But when I try to replace the "-41" with a variable, as in...
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-(MyRow-1)]C:R[-1]C)
I get an error. (I don't know anything about R1C1 style addressing -- pointers appreciated).

I can use "Cells" and/or "Range" to select the appropriate column of data. How can I put the "AVERAGE" formual in Cells( MyRow+1, MyCol ) while specifying the variable length of the column?

Thanks in advance.

 

[davesexcel]

What's the formula supposed to look like and where would you put it?

 

[Jonmo1]

Try

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-" & MyRow-1 & "]C:R[-1]C)

 

[looknsee]

What's the formula supposed to look like and where would you put it?

If the data is in Range(A1:A20), then I want to put "AVERAGE(A1:A20)" into cell A21. My challenge is that the column is not always 20 rows tall; it can be a variable number of rows tall.

 

[looknsee]

Ah -- that works. Don't know why it works (yet), but thank you.

 

[Jonmo1]

You're welcome..

You're entering a formula into a cell.
A Cell formula doesn't know what MyRow is.
It would literally end up looking like
AVERAGE(A1:AMyRow)
Which makes no sense, so it would be an error.

Basically you want to concatenate the value of MyRow into the formula.