Formula - Dynamic Range

[Guest]

Based on the current activecell, I want to create a median calculation that
will include the continuous range which is directly above the active cell.
So, if the Activecell were A10 and there was a range of data in cells A5:A9,
I would want to include in cell A10 = Median(A5:A9). The code needs to be
dynamic so that it includes the continuous range directly above the cell,
which will vary. Thanks.

Activecell.FormulaR1C1 = ?????

Jason

 

[Guest]

try this

firstrow = ActiveCell.Offset(-1, 0).End(xlUp).Row
actcolumn = ActiveCell.Column
lastrow = ActiveCell.Row - 1

ActiveCell.FormulaR1C1 = "=median(R" & firstrow & "C" & actcolumn & _
":R" & lastrow & "C" & lastrow & ")"

 

[Guest]

Joel,

That almost gets me there... it is accurately pulling the beginning row and
the ending row, but for some reason it is including more columns than just
the current column. So for example, based on my sample data of the subject
being A5:A9, instead of Median($A$5:$A$9), it is giving me Median($A$5:$Y$9).
Any suggestions on how I can modify to fix this. Thanks for your help.

 

[Guest]

I made a typo

from
ActiveCell.FormulaR1C1 = "=median(R" & firstrow & "C" & actcolumn & _
":R" & lastrow & "C" & lastrow & ")"

to
ActiveCell.FormulaR1C1 = "=median(R" & firstrow & "C" & actcolumn & _
":R" & lastrow & "C" & actcolumn & ")"

 

[Guest]

Figured it out. Made the following change and it worked. Thanks again.

ActiveCell.FormulaR1C1 = "=median(R" & firstrow & "C" & actcolumn & _
":R" & lastrow & "C" & actcolumn & ")"

 

[Dave Peterson]

Since you're in the same column:
ActiveCell.FormulaR1C1 = "=median(R" & firstrow & "C:R" & lastrow & "C)"

or
ActiveCell.FormulaR1C1 = "=median(R" & firstrow & "C:R[-1]C)"

Which means to add the value in the first row of the same column through the
cell right above the cell with the formula.