Printing time period based on usr input.

[kishlaya]

Dear all
This seems pretty simple but i dunno why i am not getting it.:dunno
i have a userform where i'll be taking user input for a specif time period.
for example if the user gives the input as Jan 2005 to may 2007, then i need to populate all the months in between(including these two) in a sheet in such a manner that the top most cell is Jan 2005 , the cell just "below" it feb 2005 and so on till may 2007.
i am pretty clueless how to do it. any help will be useful.
Thanks in advance.
Kish

[Bob Phillips]

Public Sub Test()
Dim StartDate As Date
Dim EndDate As Date
Dim i As Long

StartDate = InputBox("Supply start date in form dd/mm/yyyy")
If StartDate <> 0 Then

EndDate = InputBox("Supply end date in form dd/mm/yyyy")
If EndDate <> 0 Then

For i = Month(StartDate) To Month(EndDate)

Cells(i - Month(StartDate) + 1, "A").Value = Format(DateSerial(Year(StartDate), i, 1), "mmm yyyy")
Next i
End If
End If
End Sub

[kishlaya]

Hi xld
Thanks for the prompt reply.[:)] it is working as desired except that the user can also give the time period betwwen two years like aug 2005 to may 2006, so the output shd contain all the months(aug05, sep05.....jan06,feb06...sep06). the data set is starting from the yeear 1995 onwards,
is it possible to do?
thanks in advance

[Bob Phillips]

What exactly are you entering into the inputbox that returne the year 1995?

[kishlaya]

i am entering the date in dd/mm/yyyy format in the input box.

[Bob Phillips]

So you enter 01/08/2005 and it gets used as if it were 01/08/1995? Is that what you are saying?

[kishlaya]

not exactly, whatever the input i give it gives me only the months for that particular year, for example
if i give 01/02/2000 to 01/02/2002, it gives me upto december 2000 whereas i want all the months from feb00 till feb02.

[kishlaya]

i got it working but is there can be a better way than this?

Private Sub CommandButton1_Click()
from_day = 1
to_day = 31
from_month = CbFromMonth.Value
from_year = CbFromYear.Value
to_month = CbToMonth.Value
to_year = CbToYear.Value




Select Case from_month
Case "January"
from_month = 1
Case "February"
from_month = 2
Case "March"
from_month = 3
Case "April"
from_month = 4
Case "May"
from_month = 5
Case "June"
from_month = 6
Case "July"
from_month = 7
Case "August"
from_month = 8
Case "September"
from_month = 9
Case "October"
from_month = 10
Case "November"
from_month = 11
Case "December"
from_month = 12
End Select

Select Case to_month
Case "January"
to_month = 1
Case "February"
to_month = 2
Case "March"
to_month = 3
Case "April"
to_month = 4
Case "May"
to_month = 5
Case "June"
to_month = 6
Case "July"
to_month = 7
Case "August"
to_month = 8
Case "September"
to_month = 9
Case "October"
to_month = 10
Case "November"
to_month = 11
Case "December"
to_month = 12
End Select

from_date = DateSerial(from_year, from_month, from_day)
to_date = DateSerial(to_year, to_month, to_day)
j = 8
For i = from_date To to_date - 1
Cells(j, 1).Value = MonthName(Month(i)) & Year(i)
Cells(j, 1).Font.Bold = True
j = j + 1
Next i
Range("A8:A1200").RemoveDuplicates Columns:=1, Header:=xlNo
Unload Me
End Sub

[Bob Phillips]

Public Sub Test()
Dim StartDate As Date
Dim EndDate As Date
Dim i As Long

StartDate = InputBox("Supply start date in form dd/mm/yyyy")
If StartDate <> 0 Then

EndDate = InputBox("Supply end date in form dd/mm/yyyy")
If EndDate <> 0 Then

For i = Month(StartDate) To Month(StartDate) + DateDiff("M", StartDate, EndDate)

Cells(i - Month(StartDate) + 1, "A").Value = Format(DateSerial(Year(StartDate), i, 1), "mmm yyyy")
Next i
End If
End If
End Sub


Can you explain where 1995 comes into this?

[kishlaya]

Dear XLD
if you have seen my post i said my "data set" contains years from 1995 and not your code. the only problem which i was getting from the code is that it was printing the mi\onth only for a single year even if i give the range for two or more years.
Thanks you for helping me out,:friends:
Regards,
Kishlaya

[Bob Phillips]

Oky, I understand. I thought that you were saying you entered a year of 2005 and it output as 1995.