variables in formulas

[takuto.yamada]

I'm trying to put in formulas for a row of cells. The row location is
a variable. The formula is an average of a variable number of rows
above it. So, there are variables within the formula. After setting
the formula for the first column, I just want to fill right with that
formula, so the appropriate column is filled.

The problem is that this command does not seem to put cell references
for StartCell and EndCell.

Dim RL, C, R, RLAST As Integer
Dim StartCell As Range
Dim EndCell As Range


ActiveSheet.Range("c" & R).Formula =
"=if(ISERROR(AVERAGE(StartCell,EndCell)),"""",AVERAGE(StartCell,EndCell))"


Also tried using
ActiveSheet.Range("C" & R).FormulaR1C1
but couldn't figure out if that it really the way to go.

 

[Tom Ogilvy]

ActiveSheet.Range("c" & R).Formula = _
"=if(ISERROR(AVERAGE(" & _
StartCell.Address & ":" & EndCell.Address & ")),"""",AVERAGE(" & _
StartCell.Address & ":" & EndCell.Address & "))"

 

[takuto.yamada]

ActiveSheet.Range("c" & R).Formula = _
"=if(ISERROR(AVERAGE(" & _
StartCell.Address & ":" & EndCell.Address & ")),"""",AVERAGE(" & _
StartCell.Address & ":" & EndCell.Address & "))"

Thank you so much Tom!