• Status: Solved
• Priority: Medium
• Security: Public
• Views: 235

# 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
``````
FiscCal.xls
0
csehz
• 6
• 4
2 Solutions

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
0

IT 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,
0

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
0

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

Run-time error 1004, Application-defined or object-defined error
0

Error ... wjhat line and which version of excel then because my test worked.

Chris
0

IT 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
``````
0

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
0

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
0

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
0

IT 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.