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

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
0
csehz
Asked:
csehz
  • 6
  • 4
2 Solutions
 
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
0
 
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,
0
 
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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

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

Chris
0
 
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

0
 
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
0
 
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
0
 
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
0
 
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now