Link to home
Start Free TrialLog in
Avatar of csehz
csehzFlag for Hungary

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of csehz

ASKER

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,
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
Avatar of csehz

ASKER

Thanks I have tried, but unfortunately it brings error message

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

Chris
Avatar of csehz

ASKER

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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
Avatar of csehz

ASKER

:-) 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