PDA

View Full Version : Dynamic Conditonal Statements Help



BeachBum
08-03-2016, 08:00 PM
Hi,

I am trying to set up a code that will request the user to input a number of sessions and the appropriate times the sessions occur at (this part of the code has already been done with the help of some people on this forum. Thanks again!). The session number and start and end times are stored in an array called Schedule.

Several other bits of code are executed and then the code should proceed through each row of the data and for each time (in Column C) it should apply a session number corresponding to the timeslot that the time fits into, in a new column. ie if timeslot 2 is entered initially as 9:00 to 10:00 and a time in the data is 9:45, then the next column will display 2.
As the number of sessions and their start and finish times are unknown, this makes this part difficult.

I have tackled this process by using a For loop to cycle through each row until the last row of data (this is also unknown). Within this is a For loop and IF statement to cycle through each session number and check if the time column falls into that timeslot. If yes it writes the session number into the new column.

Unfortunately for me, it gets to the If statement and states "Out of Stack Space". Any ideas on what is wrong here? I have only just started coding and am unsure of the optimal methods for calling functions (and many other things!) so I imagine I have missed quite a few points in this part of the code.16781

Thanks in advance.

SamT
08-03-2016, 09:12 PM
Not enough info.

Run this macro after you run the Sessions userform to give us a schedule to play with


Sub CopyScheduleToSheet()
Sheets("Sheet1").Range("A1").Resize(UBound(SessionSchedule(1)), 2) = SessionSchedule
End Sub

You are probably running out of Stack space because you are calling the Function "Schedule" too often in the same sub.
try declaring a Variant and assigning the function to it.


Sub AddSessNum()
Dim mySchedule As Variant
mySchedule = Schedule
Then use the local Variable "mySchedule" in your code.

Annnnnnnnnnnnnny Way:

Sub AddSessNum()
Dim ic As Long
Dim rw As Long 'always declare Row and Column Counters as Long
Dim arrTemp As Variant

arrTemp = Schedule

With Worksheets("Raw Data") 'Note dots before Cells and Ranges in With Block
'Insert new column to populate
.Range("D:D").Insert
'Establish "For" loop to loop thru appropriate Rows
For rw = 2 To .Cells(.Rows.Count, "C").End(xlUp).Row
'Establish for loop to cycle the number of sessions
For ic = 1 To UBound(arrTemp(1))
'Establish If statement to determine if time sits in that timeslot for that session/timeslot number
If .Cells(rw, "C") >= arrTemp(ic, 1) Then
If.Cells(rw, "C") <= arrTemp(ic, 2) Then 'adds a wee bit of efficiency
'Write timeslot number in column D
.Cells(rw, "D") = ic
Exit For 'Adds a wee bit of efficiency
End If
end If
Next
Next
End With
End Sub

SamT
08-03-2016, 09:32 PM
For maximum efficiency (without using another Array and a WorksheetFunction:)

Sub AddSessNum()
Dim ic As Long
Dim arrTemp As Variant
Dim Cel As Range

arrTemp = Schedule 'If this is run after closing the UserForm you can use SessionSchedule
'Of course, after the Form is closed, any code in it is unavailable, so you need to move this to a Standard Module.

With Worksheets("Raw Data")
.Range("D:D").Insert
For Each Cel In Range(.Range("C2"), .Cells(Rows.Count, "C").End(xlUp))
For ic = 1 To UBound(arrTemp(1))
If Cel >= arrTemp(ic, 1) Then
If Cel <= arrTemp(ic, 2) Then
Cel.Offset(, 1) = ic
Exit For
End If
End If
Next
Next
End With
End Sub

BeachBum
08-04-2016, 12:05 AM
Thanks. I think my problem is due to the "Schedule" array not being passed to the module as several items are coming up as empty.

Is there a efficient method of passing the array from the userform to the standard module other than global declarations? I have read a few other posts relating to passing arrays but they aren't making sense to me.

Cheers

SamT
08-04-2016, 05:19 AM
Unless you modified your code much since your last upload, the problem is you have set up a recursive loop: Function Schedule calls Sub AddSessNum calls Function Schedule calls Sub AddSessNum calls Function Schedule calls Sub AddSessNum until you run out of stack space.

The Schedule function should do absolutely nothing except return the schedule array

The attached below works as I think you intend.

I took all the Calls out of the Function and moved them to
Private Sub cbutClose_Click()
Globals.SessionSchedule = Schedule 'Moved Variable to Standard Module "Globals"
PrintSchedule
AddSessNum (Schedule) 'Passing Schedule directly to the Procedure.Globals' Variable not used

Unload Me 'this the last line processed, no matter where in the code it is placed.
'ThisWorkbook.test
End Sub

I modified AddSessNum as indicated herein and moved it to a Standard Module, "modSht_RawData"

Sub AddSessNum(SessionSchedule As Variant) '<<<<<<<<<<<<<<<<<<<<<<<<
'SessionSchedule is declared a local variable in the above line
Dim ic As Long
Dim arrTemp As Variant
Dim Cel As Range

arrTemp = SessionSchedule '


With Worksheets("Raw Data")
.Range("D:D").Insert
.Range("D:D").NumberFormat = "###"'<<<<<<<<<<<<<<<<<<<<<<<
For Each Cel In Range(.Range("C2"), .Cells(Rows.Count, "C").End(xlUp))
For ic = 1 To UBound(arrTemp) '<<<<<<<<<<<<<<<<<<<<<<<<<
If Cel >= CDate(arrTemp(ic, 1)) Then
If Cel <= CDate(arrTemp(ic, 2)) Then
Cel.Offset(, 1) = ic
Exit For
End If
End If
Next
Next
End With
End Sub

I am showing two ways to use the function"Schedule" since I don't know where you are taking this. The variable in Globals is good as long as the Workbook is open and you have not stopped code execution, even after the Form is closed.

BeachBum
08-04-2016, 07:30 PM
That makes a lot more sense now.

and the code does exactly what I had envisaged!

Brilliant! Thank you!