VBA Excel 2000 - Calculate fiscal months

Dear Experts,

Can you please have look at the attached file, the macro also is copied to the Code, basically I am trying to calculate the current company fiscal month with a two dimensional array.

I have two probelms with it
- in a loop I have such row If FiscCal(i, 1) <= Date And FiscCal(i, 2) >= Date Then SearchedMonth = i, but it never gives value to variable SearchedMonth
- at the end of the code would like to apply a calculation in B2 with row Worksheets("Sheet1").Range("B2").Formula = "=IF(A2<Date(FiscCal(SearchedMonth,1)),""OVD"",SearchedMonth)" but that brings error

Could you advise about these, the Date gives 2011-04-04  in the immediate window so I am not sure what is incorrect for the first one as base

thanks,



Sub FiscCalTest()

Dim FiscCal(12, 12)
FiscCal(1, 1) = "2011-01-03"
FiscCal(1, 2) = "2011-02-06"
FiscCal(2, 1) = "2011-02-07"
FiscCal(2, 2) = "2011-03-06"
FiscCal(3, 1) = "2011-03-07"
FiscCal(3, 2) = "2011-04-03"
FiscCal(4, 1) = "2011-04-04"
FiscCal(4, 2) = "2011-05-08"
FiscCal(5, 1) = "2011-05-09"
FiscCal(5, 2) = "2011-06-05"
FiscCal(6, 1) = "2011-06-06"
FiscCal(6, 2) = "2011-07-03"
FiscCal(7, 1) = "2011-07-04"
FiscCal(7, 2) = "2011-08-07"
FiscCal(8, 1) = "2011-08-08"
FiscCal(8, 2) = "2011-09-04"
FiscCal(9, 1) = "2011-09-05"
FiscCal(9, 2) = "2011-10-02"
FiscCal(10, 1) = "2011-10-03"
FiscCal(10, 2) = "2011-11-06"
FiscCal(11, 1) = "2011-11-07"
FiscCal(11, 2) = "2011-12-04"
FiscCal(12, 1) = "2011-12-05"
FiscCal(12, 2) = "2011-12-31"

Dim SearchedMonth As Integer
Debug.Print Date

For i = 1 To 12

If FiscCal(i, 1) <= Date And FiscCal(i, 2) >= Date Then SearchedMonth = i

Next i

Debug.Print SearchedMonth

'Worksheets("Sheet1").Range("B2").Formula = "=IF(A2<Date(FiscCal(SearchedMonth,1)),""OVD"",SearchedMonth)"



End Sub

Open in new window

FiscCal.xls
LVL 1
csehzIT consultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
fiscal comprises objects not dates so one solution is to use:


If CDate(FiscCal(i, 1)) <= Date And CDate(FiscCal(i, 2)) >= Date Then SearchedMonth = i

Chris

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
csehzIT consultantAuthor Commented:
Chris thanks it works fine,

Do you have maybe idea to the second question, how could be put the SearchedMonth variable to the Date formula

Worksheets("Sheet1").Range("B2").Formula = "=IF(A2<Date(FiscCal(SearchedMonth,1)),""OVD"",SearchedMonth)"

thanks,
Chris BottomleySoftware Quality Lead EngineerCommented:
Note alternatively:

Dim FiscCal(12, 12) As Date

and for the formula try:


Worksheets("Sheet1").Range("B2").Formula = "=IF(A2<Date(" & FiscCal(SearchedMonth, 1) & "),""OVD""," & SearchedMonth & ")"

Chris
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

csehzIT consultantAuthor Commented:
Thanks I have tried, but unfortunately it brings error message

Run-time error 1004, Application-defined or object-defined error
Chris BottomleySoftware Quality Lead EngineerCommented:
Error ... wjhat line and which version of excel then because my test worked.

Chris
csehzIT consultantAuthor Commented:
Chris I used the attached code, which gives message at this line

Worksheets("Sheet1").Range("B2").Formula = "=IF(A2<Date(" & FiscCal(SearchedMonth, 1) & "),""OVD""," & SearchedMonth & ")"

My version is Excel 2000, I tried to mark it at the subject of the topic
Sub FiscCalTest()

Dim FiscCal(12, 2) As Date

FiscCal(1, 1) = "2011-01-03"
FiscCal(1, 2) = "2011-02-06"
FiscCal(2, 1) = "2011-02-07"
FiscCal(2, 2) = "2011-03-06"
FiscCal(3, 1) = "2011-03-07"
FiscCal(3, 2) = "2011-04-03"
FiscCal(4, 1) = "2011-04-04"
FiscCal(4, 2) = "2011-05-08"
FiscCal(5, 1) = "2011-05-09"
FiscCal(5, 2) = "2011-06-05"
FiscCal(6, 1) = "2011-06-06"
FiscCal(6, 2) = "2011-07-03"
FiscCal(7, 1) = "2011-07-04"
FiscCal(7, 2) = "2011-08-07"
FiscCal(8, 1) = "2011-08-08"
FiscCal(8, 2) = "2011-09-04"
FiscCal(9, 1) = "2011-09-05"
FiscCal(9, 2) = "2011-10-02"
FiscCal(10, 1) = "2011-10-03"
FiscCal(10, 2) = "2011-11-06"
FiscCal(11, 1) = "2011-11-07"
FiscCal(11, 2) = "2011-12-04"
FiscCal(12, 1) = "2011-12-05"
FiscCal(12, 2) = "2011-12-31"

Dim SearchedMonth As Integer
Debug.Print Date

For i = 1 To 12

If CDate(FiscCal(i, 1)) <= Date And CDate(FiscCal(i, 2)) >= Date Then SearchedMonth = i

Next i

Debug.Print SearchedMonth

Worksheets("Sheet1").Range("B2").Formula = "=IF(A2<Date(" & FiscCal(SearchedMonth, 1) & "),""OVD""," & SearchedMonth & ")"


End Sub

Open in new window

Chris BottomleySoftware Quality Lead EngineerCommented:
OOPS

Wrong code somehow:


Worksheets("Sheet1").Range("B2").Formula = "=IF(A2<Datevalue(""" & FiscCal(SearchedMonth, 1) & """),""OVD""," & SearchedMonth & ")"

Try again as above which is hopefuilly correct when I finish the post

Chris
Chris BottomleySoftware Quality Lead EngineerCommented:
The code I posted was not as tested, I must have 'undone' most of the edits!

LOoking at the post above it seems to reflect what I tested so hopefully!

Chris
Chris BottomleySoftware Quality Lead EngineerCommented:
Confuses me so ...

The code I posted MUCH earlier was not as tested, I must have 'undone' most of the edits as part of the copy action!

I have posted in http:#35312855  the correct code, and took a Look at the post immediately afterwards to make a sanity check of it and this led to the statement "it seems to reflect what I tested" so hopefully when you try that line it will do what you want.

Chris
csehzIT consultantAuthor Commented:
:-) Chris thanks very much, it works fine with your second formula.

Thanks just again, you helped me more than just this actual case, as I never knew how to put array value as variable to a formula
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.