Functions using named ranges.....
Posted by Barry Ward on August 17, 2001 4:30 AM
I f I record the macro to run the percentile function I get the following code:
ActiveCell.FormulaR1C1 = _
"=PERCENTILE('[Jenna Marie.xls]Stats(270401)'!R2C1:R134C1,0.05)"
I then want it to run on an already named range. but if I replace the address with my range it doesn't want to know.
This is the section of code in which I want to insert it:
ActiveWorkbook.Names.Add Name:="CL", RefersTo:="=OFFSET($A$2,0,0,count($A:$A),1)"
Range("CL").Select
'Deletes column B
Columns("B:B").Select
Selection.delete Shift:=xlToLeft
'Runs the descriptive statistics module
Run "Descr", ActiveSheet.Range("CL"), _
ActiveSheet.Range("$B$1"), "C", False, True, , , 95
'Application.Run "ATPVBAEN.XLA!Descr", ActiveSheet.Range("CL"), _
'ActiveSheet.Range("$E$1"), "C", False, True, , , 95
' Calculates Percentiles
Range("$B$17") = "5th Percentile"
Range("$B$18") = "95th Percentile"
Range("$C$17").Formula = "=PERCENTILE(range("CL"),0.05)"
Range("$C$18").Formula = "=PERCENTILE(CL,0.95)"