variables in formulas

T

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.
 
T

Tom Ogilvy

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

takuto.yamada

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

Thank you so much Tom!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top