Chris, Thanks a lot. I would've spent weeks (not to say months) to figure this out.
Tks again, Art. On Feb 21, 12:11 pm, Chris Spicer <chris.spi...@technicana.com> wrote: > Hi Art, > > Contract codes are always a pain. The following functions generate > the previous n codes: > > Option Explicit > > Private MonthCodes() As String > > Public Function GetExpiredCodes(currentContract As String, > noOfPreviousContracts As Integer) As String() > Dim startMonthCode As String > Dim monthCode As Integer > Dim year As Integer > Dim previousContracts() As String > ReDim previousContracts(1 To noOfPreviousContracts) > > ' Split code out into month and year > startMonthCode = Left(currentContract, 1) > year = Right(currentContract, 1) > > AssignMonthCodes > > ' Match our month code to an integer value > Dim i As Integer > For i = 1 To 12 > If MonthCodes(i) = startMonthCode Then > monthCode = i > End If > Next i > > For i = 1 To noOfPreviousContracts > ' Roll through the months > monthCode = monthCode - 1 > If (monthCode = 0) Then > monthCode = 12 > ' Roll through the years > year = year - 1 > If (year < 0) Then > year = 9 > End If > End If > > previousContracts(i) = MonthCodes(monthCode) & year > Next i > > GetExpiredCodes = previousContracts > End Function > > Private Sub AssignMonthCodes() > ReDim MonthCodes(1 To 12) > > MonthCodes(1) = "F" > MonthCodes(2) = "G" > MonthCodes(3) = "H" > MonthCodes(4) = "J" > MonthCodes(5) = "K" > MonthCodes(6) = "M" > MonthCodes(7) = "N" > MonthCodes(8) = "Q" > MonthCodes(9) = "U" > MonthCodes(10) = "V" > MonthCodes(11) = "X" > MonthCodes(12) = "Z" > End Sub > > Regards, > > Chris Spicerwww.Technicana.com --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
