Fiscal Quarter Macro HELP Please

[danjw_98]

when using the below formula it gives the correct fiscal yr and quarter. the fiscal year starts 11/01/xx and ends 10/31/xx.

="FY"&RIGHT(YEAR(A2),2)+(MONTH(A2)>10)&CHOOSE(MONTH(A2)," Q1"," Q2"," Q2"," Q2"," Q3"," Q3"," Q3"," Q4"," Q4"," Q4"," Q1"," Q1")
-----------------------------------------------------------------------------

when i use this formula in a macro it doesn't give me the correct fiscal yr for 11/10/10 thru 01/31/11. it should be fy11 q1 but i get fy9 q1. any assistance would be appreciated...

Sub FiscalQtr()

Dim x As Long
finalrow = Range("c65536").End(xlUp).Row
For x = 2 To finalrow

y = Range("f" & x).Value
I = "FY" & Right(Year, 2) + (Month > 10) & Choose(Month, " Q1", " Q2", " Q2", " Q2", " Q3", " Q3", " Q3", " Q4", " Q4", " Q4", " Q1", " Q1")

Range("o" & x) = I

Next x

End Sub

 

[jasonb75]

Try

Sub FiscalQtr()
Dim x As Long
    finalrow = Range("c65536").End(xlUp).Row
For x = 2 To finalrow
        Range("o" & x) = Format(Application.EoMonth(Range("f" & x).Value, 2), "F\Yyy\Qq")
Next x
End Sub

 

[danjw_98]

Yes that worked... I was wondering though if you might be able to tell me why the formula would work as a formula in a cell but not as a macro, just curious.

thanks again...

 

[jasonb75]

I'm not entirely sure, although I have had similar problems in the past.

It would appear that using the method of + ( value1 > value2) gives a return of -1 instead of +1 in VBA, but I don't know if that is always the case or if other things affect it.

Personally I use alternative methods, but if all else fails, +Abs(value1 > value2) will ensure it's always a +ve value.