Hello folks,
been trying to generate contract codes for expired future contracts.
For instance, let's say I want to retrieve all expired contracts which
go back monthly (for the sake of simplification) for Light Crude (CL)
starting from today...
so today (February 20,2009) the first contract is March 2009 or H9
(full code CLH9). Now let's say I want the expired contracts which
will be G9 (february 09), F9 (january 09), Z8 (december 08) and there
so on (month table in the code).
I managed to retrieve the right month sequence but I have been unable
to start the loop in the middle (February for instance) and also to
retrieve the corresponding year.
Could any of the experts out there give me a hand? I'm sure for most
of you this is peace of cake but for a beginner like me it's still
hard.
Have a look at the code below.
Sub ExpiredCodes()
Dim lCount As Integer
Dim lNum As Integer
Dim i As Integer
Dim Mth As String
Application.ScreenUpdating = False
For i = 1 To 10
'For going back 10 years
For lCount = 12 To 1 Step -1
lNum = lNum + 1
Select Case lCount
Case 1: Mth = "F"
Case 2: Mth = "G"
Case 3: Mth = "H"
Case 4: Mth = "J"
Case 5: Mth = "K"
Case 6: Mth = "M"
Case 7: Mth = "N"
Case 8: Mth = "Q"
Case 9: Mth = "U"
Case 10: Mth = "V"
Case 11: Mth = "X"
Case 12: Mth = "Z"
End Select
Sheet1.Range("L2").Offset(lNum, 0) = Mth
Next lCount
Next i
Application.ScreenUpdating = True
End Sub
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
