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 Spicer
www.Technicana.com
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
