Formula - Dynamic Range
 

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

Formula - Dynamic Range
 

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 & ")"

Formula - Dynamic Range
 

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.

"Joel" wrote:

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 & ")"

Formula - Dynamic Range
 

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 & ")"

"Jason" wrote:

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.

"Joel" wrote:

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 & ")"

Formula - Dynamic Range
 

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

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

"Jason" wrote:

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.

"Joel" wrote:

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 & ")"

Formula - Dynamic Range
 

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.





Jason wrote:

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

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

"Jason" wrote:

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.

"Joel" wrote:

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 & ")"



--

Dave Peterson