You're very welcome. On Feb 23, 3:36 pm, Art <rombona...@gmail.com> wrote: > 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- Hide quoted text - > > - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
