Hi,

I have a program which has a User form. and which uses multiple IF statements
to determine what to do. The user enters FirstDate, SecondDate and number of
transactions to look at (NoToFill). It uses a counter to check the cell value
on one sheet and, depending on what that cell value is, does a variety of
things. Hence, the multiple IF statements. As of now I have been unable to
execute any IF statement beyond the first two. Here is a brief bit of code:
The format for the cells is General.

'Main program
Public Sub Jim()
Count = 1

For i = 1 To NoToFill
Count = Count + 1
'If first transaction then set up first line on Sheet3
If Count = 2 Then Mary1
'If other than first transactions and date on sheet2 before date entered by
user go to Fred
If Worksheets("Sheet2").Cells(Count, 2) < FirstDate Then Fred
'If other than first transactions and date on sheet2 within dates entered by
user go to Jane
If Worksheets("Sheet2").Cells(Count, 2) = FirstDate And
Worksheets("Sheet2").Cells(Count, 2) <= SecondDate Then Jane

Next i

End Sub

I can get results from "Mary1" and "Fred" but "Jane" never seems to process
anything. The error I receive is an Out of Stack Space error. Mary1 and Fred
do process if I do not enter dates. However, when I enter dates and the
program is supposed to calculate based upon the date criteria, I get an Out of
Stack error and an arrow appears next to the If Count=2 Then Mary1 line. Can
anyone tell me what might be wrong and what I should do to correct it?

Thanks

There are too many unexplained variables in your sub.
It is therefore difficult to understand what is going on.
The following is one way it could be set up...
'-------------------------------------
Option Explicit

Public Sub Jim(ByRef NoToFill As Long, ByRef FirstDate As Date, _
ByRef SecondDate As Date)
Dim i As Long

' keep things within reason
For i = 2 To WorksheetFunction.Max(NoToFill, 1000)

If i = 2 Then
Mary1
ElseIf Worksheets("Sheet2").Cells(i, 2).Value < FirstDate Then
Fred
ElseIf (Worksheets("Sheet2").Cells(i, 2).Value = FirstDate) And _
(Worksheets("Sheet2").Cells(i, 2).Value <= SecondDate) Then
Jane
End If

Next i
End Sub
'-----------------------------------
Jim Cone
San Francisco, USA


"JimFor" wrote in message
...
Hi,
I have a program which has a User form. and which uses multiple IF statements
to determine what to do. The user enters FirstDate, SecondDate and number of
transactions to look at (NoToFill). It uses a counter to check the cell value
on one sheet and, depending on what that cell value is, does a variety of
things. Hence, the multiple IF statements. As of now I have been unable to
execute any IF statement beyond the first two. Here is a brief bit of code:
The format for the cells is General.

'Main program
Public Sub Jim()
Count = 1
For i = 1 To NoToFill
Count = Count + 1
'If first transaction then set up first line on Sheet3
If Count = 2 Then Mary1
'If other than first transactions and date on sheet2 before date entered by
user go to Fred
If Worksheets("Sheet2").Cells(Count, 2) < FirstDate Then Fred
'If other than first transactions and date on sheet2 within dates entered by
user go to Jane
If Worksheets("Sheet2").Cells(Count, 2) = FirstDate And
Worksheets("Sheet2").Cells(Count, 2) <= SecondDate Then Jane
Next i
End Sub

I can get results from "Mary1" and "Fred" but "Jane" never seems to process
anything. The error I receive is an Out of Stack Space error. Mary1 and Fred
do process if I do not enter dates. However, when I enter dates and the
program is supposed to calculate based upon the date criteria, I get an Out of
Stack error and an arrow appears next to the If Count=2 Then Mary1 line. Can
anyone tell me what might be wrong and what I should do to correct it?
Thanks

Thanks. I'll try that. And yes, I know the program as it is written is
complex. What I have been trying to do is get the thing to run and then work on
my refining techniques later.

If this is helpful in any way, this is the general outline of what I am trying
to do. But I warn you, the following is rather long. And it is a bit basic.

I have three sheets of data. The first contains account numbers and account
inventory numbers for the end of a year.

The second page consists of transaction data for accounts, some of which appear
on sheet1 and some of which do not. The transaction data is basically account
unit sales and purchases by individual dates for a period of time.

One might look at it this way. I'm trying to determine the inventory status
and transaction type for each account for a period of time. The computer
program which provides the data does not keep a running total of inventor
amounts. So I can't just ask it to tell me what transactions occurred within a
period as well as tell me what the beginning and ending inventory amounts are.
I must build them up myself.

Let's say I need account transaction data for the period 2/2/01 through
2/2/02. I have to first get the ending inventory for the period 12/31/01. That
would be from sheet1. The computer program now used just keeps end of year
inventory data. Then I must add and subtract from that ending inventory
amount all units an account bought and sold for the period 1/1/01 through
2/1/01. That is the beginning inventory amount for that account. That goes on
sheet3.

Next I have to add and subtract all the units the account purchased and sold
during the period 2/2/01 through 2/2/02 from the beginning inventory amount.
That give me the account ending inventory amount. I transfer the individual
transaction data, as well as the ending inventory amount for that account, to
sheet3 and put that data under the beginning inventory amount. Repeat for each
account which had transaction data for that period.

The user inputs the two dates as well as the total number of accounts which
appears on sheet1 (for VLOOKUP purposes). The computer should do the rest.
I''ve written the various parts of the program which will do each task and
tested each individually on dummy data. They seem to work. However, when I try
to put the thing together, and add some more complexity, some things don't
work. I'm in the process of fixing them.

Thanks for the help.