Solved: Help for FY problem

[SeanJ]

Hello again folks.

I have a combo box that has FY from current year - 2 through current year +3.

I am trying to put on a worksheet a whole FY year.


example:

Oct-07 through Sep-08

Based of the ComboFY value.

[VBA]Option Explicit
Dim xDate As String 'convert date picker year to string
Dim ydate As Integer 'convert string to integer
Dim dteDate As Integer 'integer year
Dim TemDate As Integer 'for next loop counter[/VBA]

[VBA]Private Sub ComboFding_Change()
xDate = Format(DTPicker.Value, "yy")
ydate = xDate
ydate = ydate - 2
dteDate = ydate + 3
For TemDate = ydate To dteDate
If TemDate <= 9 Then
ComboFY.AddItem Format(TemDate, "0#")
Else
ComboFY.AddItem Format(TemDate, "##")
End If
Next TemDate
End Sub[/VBA]

[Kenneth Hobs]

You can use this as the single line in your For loop:
[VBA]ComboFY.AddItem Format(TemDate, "yy") [/VBA]
For a CommandButton click event:
[VBA]Range("A1").Value="Oct-" & ComboFY.Value & " through Sep-" & Format(ComboFY.Value+1, "yy") [/VBA]

[SeanJ]

Thank you for your reply.

But I failed to make something clear.

I need FY year start in A1 and end in A12.

A1 = Oct-07
.
.
.
A12 = Sep-08

It has to loop through this process

Sorry for not being clear.

[SeanJ]

I have found dateadd funtion

[vba]Mdate = "Sep" & "-" & ComboFY.Value
For numMonth = 1 To 12

ws.Cells(12, 3 + numMonth).Value = Format(DateAdd("m", numMonth, Mdate), "mmm-yy")


Next numMonth[/vba]

But having trouble with it.

ComboFY is 07 but Mdate returning 10/7/2008. I am working on it

I got it to be
[VBA]Mdate = "9" & "/" & "1" & "/" & ComboFY.Value[/VBA]
but my output value 7-Oct where it should be Oct-07

[Kenneth Hobs]

Good, you are on the right track.
[vba]Sub Test()
Dim sDate As String, i As Integer

sDate = "07"
'sDate=ComboFY.Value

For i = 1 To 12
Range("A" & i).Value = DateAdd("m", i, DateSerial(sDate + 2000, 9, 1))
Range("A" & i).NumberFormat = "mmm-yy"
Next i

End Sub[/vba]

[SeanJ]

I finally got what I am looking for.

[VBA]ws.Cells(12, 3 + numMonth).Value = Format(DateAdd("m", numMonth, DateSerial(ComboFY.Value + 2000, 9, 1)), "mmm-yyyy")[/VBA]

Thanks for the help.