I'm doing a few things here at it is my second day of using macros so im sure there is better ways of doing this. Anyway the problem is that it doesnt calculate the correct percentile look towards the bottom of the macro the range is made up of decimal values (0.323, 0.421). I need to calculate the percentile of values from a data logger so there are lots of them.
Thanks alan
Sub Calculate95anodiclines()
'initialise rngdata for range to be selected and perc for hopefully more stable version of rngdata and hope for initial selection as Range type variable
Dim rngdata As Range
Dim perc As Range
Dim Hope As Range
'initialise what wil become anodic and cathodic lines
Dim Anodic As Long
Dim Cathodic As Long
'set hope to initial selelction maybe a good idea for future macros
Set Hope = Selection
'initialise x as long
Dim x As Long
Dim y As Long
'set initial value of x and ythis is based on using relative cell refernces in appropriate column for do while loops
x = 0
y = 0
'sets instructions based on the active cell not being blank
Do While ActiveCell.Value <> ""
'increases value of x by as x is going to be used to define selection
x = x + 1
' advances to the next cell
ActiveCell.Offset(1).Select
Loop
'defines range data relative to x and active cell
Set rngdata = Range(ActiveCell.Offset(-x), ActiveCell.Offset(-1))
'Selects range rng data
rngdata.Select
'defines new range perc as equal to rng data hopefully will not change as x changes or something
Set perc = Selection
'calculates 95% anodic and cathodic vaues
Anodic = Application.WorksheetFunction.Percentile(perc, 0.95)
Cathodic = Application.WorksheetFunction.Percentile(perc, 0.95)[/B][/B]
'sets instructions based on the active cell not being blank, ie percentile and -850 columns extended while there is a datalogged value
Do While ActiveCell.Value <> ""
' offsets anodic and cathodic values by 3 and 4 columns
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = Anodic
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = Cathodic
'inserts an -850 value ready for extension with other three values to form series for graph
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = -850
' increments y count to advance to the next cell
y = y + 1
Hope.Offset(y, 0).Range("A1").Select
Loop
MsgBox "Bibbity Bobbity"
End Sub
Bookmarks