Solved: HELP me figure out how to use the formula commands

[deeeeroy]

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.

[herzberg]

I think this may help; I didn't try it, though. Use this to replace the current line where you specify the formula.
ActiveCell.FormulaR1C1 = "=MEDIAN(R1C2:R" & .Cells(1, 2).End(xlDown).Row & "C2)"

[shades]

Howdy, and welcome to the board. Is this question specific to Mac VBA?

[deeeeroy]

Herzberg,

Thanks, I think what you told me is along the right line. And it help me think of this

Sub median()
'
' Median Macro
' Macro recorded 2/4/2008 by Daniel Mower
'
Dim RowNum As String
Range("B1").Select
Selection.End(xlDown).Offset(2, 0).Select
RowNum = (ActiveCell.Row - 1) * -1
ActiveCell.FormulaR1C1 = RowNum '"MEDIAN"
ActiveCell.Next.Select
ActiveCell.FormulaR1C1 = "=MEDIAN(R[" & RowNum & "]C:R[-2]C)"
Selection.Copy
Range("C110:N110").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Copy
End Sub


THANKS!!!!!!!!