title: Adding a Trailing Twelve Months array feature to VBA code
body: <p>I have an existing code which is a function that yields an array:
  Example input: <code>=cellrange(B5,"ytd")</code> [where from <code>B5</code> and below (or above) there are dates]
  Example output: <code>$B$129:$B$280</code> which is the full date range for this year in column <code>B</code></p>
  
  <p>I am trying to add a new <code>case</code> called <code>ttm</code> (trailing twelve months), however I am struggling to find a way to incorporate it.</p>
  
  <p>The <code>ttm</code> case should show yield a trailing 12 months range from the latest available date</p>
  
  <pre><code>Option Explicit
  
  Public Function cellrange(rDates As Range, vFilter As Variant, Optional colOffsetA As Variant, Optional colOffsetB As Variant) As String
  'DESCRIPTION:
      'This function takes any cell value in a row and a input: YTD, ALL, or any year (i.e. 2014, 2015) and it finds the range in which the date is situated
  
      Dim i As Long, ndx1 As Long, ndx2 As Long, r As Range, vA As Variant, bErr As Boolean, bAll As Boolean
      bErr = True
      If IsDate(rDates) Then
          With rDates.EntireColumn
              i = rDates.Parent.Evaluate("count(" &amp; .Address &amp; ")")
              Set r = .Cells(1 - i + rDates.Parent.Evaluate("index(" &amp; .Address &amp; ",match(9.9E+307," &amp; .Address &amp; "))").row).Resize(i, 1)
          End With
          vA = r.Value
          If IsMissing(colOffsetA) And IsMissing(colOffsetB) Then
              colOffsetA = 0: colOffsetB = 0
          End If
          If IsMissing(colOffsetB) = True Then colOffsetB = colOffsetA
          Select Case LCase(vFilter)
              Case "all"
                  bErr = 0: bAll = 1
                  Set r = r.Range(r.Parent.Cells(1, 1 + colOffsetA), r.Parent.Cells(r.Count, 1 + colOffsetB))
              Case "ytd"
                  For i = 1 To UBound(vA)
                      If ndx1 = 0 And Year(vA(i, 1)) = Year(Date) Then ndx1 = i
                      If vA(i, 1) &lt;= Date Then ndx2 = i
                  Next
              Case Else 'year
                  vFilter = Val(vFilter)
                  If vFilter Then
                      For i = 1 To UBound(vA)
                          If ndx1 = 0 And Year(vA(i, 1)) = vFilter Then ndx1 = i
                          If ndx1 And Year(vA(i, 1)) = vFilter Then ndx2 = i
                      Next
                  End If
          End Select
          If Not bAll Then If ndx1 &gt; 0 And ndx2 &gt; 0 Then Set r = r.Range(r.Parent.Cells(ndx1, 1 + colOffsetA), r.Parent.Cells(ndx2, 1 + colOffsetB)): bErr = False
          If Not bErr Then cellrange = r.Address Else cellrange = CVErr(xlErrValue)
      Else
          cellrange = CVErr(xlErrValue) 'check if this is the correct error handling
      End If
  End Function
  </code></pre>