deeeeroy
02-05-2008, 04:03 PM
I am currently writing a macro that will calculate the median of the array of values above my active cell. Currently I have the following code:
Sub median()
'
' Median Macro
' Macro recorded 2/4/2008 by Daniel Mower
'
Range("B1").Select
Selection.End(xlDown).Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "MEDIAN"
ActiveCell.Next.Select
ActiveCell.FormulaR1C1 = "=MEDIAN(R[-109]C:R[-2]C)"
ActiveCell.Select
Selection.Copy
Range("C110:N110").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Copy
End Sub
All this is grand, and does what I would like it to do. However, I need the size of the array to be exspansive. The FormulaR1C1 feature takes in a string. I need to somehow have the formula Calculate the median of the all the cells above.
Sub median()
'
' Median Macro
' Macro recorded 2/4/2008 by Daniel Mower
'
Range("B1").Select
Selection.End(xlDown).Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "MEDIAN"
ActiveCell.Next.Select
ActiveCell.FormulaR1C1 = "=MEDIAN(R[-109]C:R[-2]C)"
ActiveCell.Select
Selection.Copy
Range("C110:N110").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Copy
End Sub
All this is grand, and does what I would like it to do. However, I need the size of the array to be exspansive. The FormulaR1C1 feature takes in a string. I need to somehow have the formula Calculate the median of the all the cells above.