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)"
Posted by neo on August 17, 2001 7:31 AM
i'm guessing at this but i think your problem may lie in the type of code your macro recorded. the .formulaR1C1 property uses a different addressing style when entering formulas into cells. trying to use a named range in r1c1 format shouldn't work. but, if you look further down towards the bottom of the code you posted you'll see the .formula property which uses a1 style notation. using that property instead, you should be able to perform the same function as you're looking for but with a named range as the addressing type.
hope that helped...
neo
