PDA

View Full Version : Solved: Help for FY problem



SeanJ
08-18-2008, 07:39 AM
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.

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

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

Kenneth Hobs
08-18-2008, 08:34 AM
You can use this as the single line in your For loop:
ComboFY.AddItem Format(TemDate, "yy")
For a CommandButton click event:
Range("A1").Value="Oct-" & ComboFY.Value & " through Sep-" & Format(ComboFY.Value+1, "yy")

SeanJ
08-18-2008, 08:46 AM
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
08-18-2008, 09:35 AM
I have found dateadd funtion

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

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


Next numMonth

But having trouble with it.

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

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

Kenneth Hobs
08-18-2008, 09:49 AM
Good, you are on the right track.
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

SeanJ
08-18-2008, 10:29 AM
I finally got what I am looking for.

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

Thanks for the help. :wavey: