title: How do I select subrange from a range object in VBA?
body: <p>I am trying to write a VBA function that would give me the average and stdev of a fractile of data (assuming total 5 fractiles). The data would be either a row vector or a Column vector. I have written down the following till now :</p>
  
  <pre><code>    Function AverageFractile(ret As Range, fractile As Integer) As Variant
  
      Dim lenRange As Integer
      Dim startIndex As Integer
      Dim endIndex As Integer
      Dim subRange As Variant
  
      'Arrange the range object in ascending Order, doesn't work
      ' ret = Sort(ret, XlSortOrder = xlAscending)
  
      'Getting the indices that would be used to slice the input range to get the relevant fractile
      lenRange = Application.WorksheetFunction.Max(ret.Rows.Count, ret.Columns.Count)
      startIndex = (lenRange * (fractile - 1)) \ 5 + 1
      endIndex = (lenRange * fractile) \ 5
  
  '    subRange = ret(Cells(startIndex,1),Cells(endIndex,1))
  '    This is not working 
  
  
      End Function
  </code></pre>
  
  <p>I am stuck at two places till now:-
  a) I am trying to sort the data, but the function Sort is not working, how do I sort the range object which is the input in ascending order?</p>
  
  <p>b) How do I select a subrange from range object, so that I can calculate its average and Stdev?</p>
  
  <p>Appreciate the help, I have been trying for a couple of hours now and haven't been able to work it out.</p>