csehz
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").For mula = "=IF(A2<Date(FiscCal(Searc hedMonth,1 )),""OVD"" ,SearchedM onth)" 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,
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note alternatively:
Dim FiscCal(12, 12) As Date
and for the formula try:
Worksheets("Sheet1").Range ("B2").For mula = "=IF(A2<Date(" & FiscCal(SearchedMonth, 1) & "),""OVD""," & SearchedMonth & ")"
Chris
Dim FiscCal(12, 12) As Date
and for the formula try:
Worksheets("Sheet1").Range
Chris
ASKER
Thanks I have tried, but unfortunately it brings error message
Run-time error 1004, Application-defined or object-defined error
Run-time error 1004, Application-defined or object-defined error
Error ... wjhat line and which version of excel then because my test worked.
Chris
Chris
ASKER
Chris I used the attached code, which gives message at this line
Worksheets("Sheet1").Range ("B2").For mula = "=IF(A2<Date(" & FiscCal(SearchedMonth, 1) & "),""OVD""," & SearchedMonth & ")"
My version is Excel 2000, I tried to mark it at the subject of the topic
Worksheets("Sheet1").Range
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
ASKER
Do you have maybe idea to the second question, how could be put the SearchedMonth variable to the Date formula
Worksheets("Sheet1").Range
thanks,