Hey all,
This will take some explaining - you may want to get a fresh source of caffeine before I put you to sleep on this one :wink:
I have some code that requires a ton of loops - and it's making the macro take a very long time to run (ballpark 2 hours on a simplified version). The good news is that the code does appear to work ok (no infinite loops, results look reasonable). Basically I'm hoping for any advice to shorten the run time. Any input like "why the heck don't you just do it like this.." would be great also.
Before I post what I have built in my spreadsheet let me describe the situation I'm in:
[the situation I'm in]
Basically I'm trying to create an Optimized Schedule for a Call Center <insert groan here>.
Actually most of the complexities in staffing for this kind of thing have already been handled. My company bought an app that spits out a "Staffing Model", basically it outputs a column of numbers that represent "the ideal number of people you need to schedule to handle the calls for each half-hour over the course of the week".
Unfortunately, it doesn't try to find an optimized schedule, one that translates that staffing model to an actual Schedule that minimizes overstaffing and understaffing (under whatever contraints). Management has been asking questions like "What's the minimum overstaffing we can do while staying at most x% understaffed if we change to a Z schedule setup?" Currently to get those results requires a lot of manual Trial and Error - so I'm trying to automate this a bit.
[/the situation I'm in]
Ok here's the setup of my spreadsheet (all data is on one sheet called "SchedulingOptimization")
I have a Table of Schedule Times in Range $A$360:$AW$553
-A360:A360 contains the half-hour labels of a 24 hour period
-A360:A553 contains a list of shedule numbers 1 through 193
-the table contains X's to signify half hours that are part of each schedule
I also have an Input Shift Table in Range $B$4:$CX$11
-B5:B11 represent days of the week, Sunday through Saturday
-C4:CX4 contain (up to 100 call-takers)
-this is the input table. For example typing "44" in cell D5 indicates that you want to set Call Taker #2 on Shift #44 for Sunday.
I have a 3rd table that graphically displays the schedule as you change values in the input table (kind of like a Gantt chart (sp?)). $B$15:$CX$351
-C15:CX15 are the call-takers (identical to C4:CX4)
-B16:B351 are the all the half hours of the week
-the table mostly contains Index/Match formulas to pull X's from the Schedule Table.
-The typical formula looks like this:
=INDEX($B$360:$AW$553,C$5+1,MATCH($B16,$B$360:$AW$360,0))
In Range CZ16:ZC351 I calculate the number of callers per half hour example =Countif(B16:CX16,"x")
In Range DA16:DA351 I have the staffing model (the output of the scheduled staff I theoretically need).
In Range DC16:DC361 I calculate the error between the two. (Overstaffing on the half hour indicates a positive error, Understaffing gives a negative error).
Range DA357 contains the sum of the total error. It looks like:
=SUMIF(DC16:DC351,">0")-(SUMIF(DC16:DC351,"<0")*9)+(COUNTIF(CZ16:CZ351,0)*100000)
This is the number I'm trying to minimize (you can see that understaffing is considered 9x worse than overstaffing, also there's an error penalty for having any time periods scheduled with 0 call-takers).
So basically, as you toss in shifts for technicians you can play around with the minimum error. The code I'm using is an attempt to find the "Most ideal" schedule possible.
Basically the macro runs these steps:
1)Turns off Screenupdating and autocalculation
2)Copies a range that starts the model to a basic starting point (all half hours covered, but has tons of overstaffing and understaffing to minimize)
3)Identifies the maximum number of call takers you want to schedule
4)Starts to loop
-Loop starts with the last call taker slot
-For each technician, loop through each shift, for 21 combinations of workdays
-After each loop, calculate the error and compare it to the smallest error found so far
Here is the code (a simplified version) of what I've got so far.
That's basically it - I'm not sure what do do from here to shorten the macro's run time.
Any suggestions?
(Also keep in mind this is a simplified version - I'm actually going to try to toss in 10 hour days, split shifts (yech), and so on. I've looked a little into the Solver - but I'm not sure if that's the way to go).
Anyway - I appreciate any assistance you can offer!
Adam
This will take some explaining - you may want to get a fresh source of caffeine before I put you to sleep on this one :wink:
I have some code that requires a ton of loops - and it's making the macro take a very long time to run (ballpark 2 hours on a simplified version). The good news is that the code does appear to work ok (no infinite loops, results look reasonable). Basically I'm hoping for any advice to shorten the run time. Any input like "why the heck don't you just do it like this.." would be great also.
Before I post what I have built in my spreadsheet let me describe the situation I'm in:
[the situation I'm in]
Basically I'm trying to create an Optimized Schedule for a Call Center <insert groan here>.
Actually most of the complexities in staffing for this kind of thing have already been handled. My company bought an app that spits out a "Staffing Model", basically it outputs a column of numbers that represent "the ideal number of people you need to schedule to handle the calls for each half-hour over the course of the week".
Unfortunately, it doesn't try to find an optimized schedule, one that translates that staffing model to an actual Schedule that minimizes overstaffing and understaffing (under whatever contraints). Management has been asking questions like "What's the minimum overstaffing we can do while staying at most x% understaffed if we change to a Z schedule setup?" Currently to get those results requires a lot of manual Trial and Error - so I'm trying to automate this a bit.
[/the situation I'm in]
Ok here's the setup of my spreadsheet (all data is on one sheet called "SchedulingOptimization")
I have a Table of Schedule Times in Range $A$360:$AW$553
-A360:A360 contains the half-hour labels of a 24 hour period
-A360:A553 contains a list of shedule numbers 1 through 193
-the table contains X's to signify half hours that are part of each schedule
I also have an Input Shift Table in Range $B$4:$CX$11
-B5:B11 represent days of the week, Sunday through Saturday
-C4:CX4 contain (up to 100 call-takers)
-this is the input table. For example typing "44" in cell D5 indicates that you want to set Call Taker #2 on Shift #44 for Sunday.
I have a 3rd table that graphically displays the schedule as you change values in the input table (kind of like a Gantt chart (sp?)). $B$15:$CX$351
-C15:CX15 are the call-takers (identical to C4:CX4)
-B16:B351 are the all the half hours of the week
-the table mostly contains Index/Match formulas to pull X's from the Schedule Table.
-The typical formula looks like this:
=INDEX($B$360:$AW$553,C$5+1,MATCH($B16,$B$360:$AW$360,0))
In Range CZ16:ZC351 I calculate the number of callers per half hour example =Countif(B16:CX16,"x")
In Range DA16:DA351 I have the staffing model (the output of the scheduled staff I theoretically need).
In Range DC16:DC361 I calculate the error between the two. (Overstaffing on the half hour indicates a positive error, Understaffing gives a negative error).
Range DA357 contains the sum of the total error. It looks like:
=SUMIF(DC16:DC351,">0")-(SUMIF(DC16:DC351,"<0")*9)+(COUNTIF(CZ16:CZ351,0)*100000)
This is the number I'm trying to minimize (you can see that understaffing is considered 9x worse than overstaffing, also there's an error penalty for having any time periods scheduled with 0 call-takers).
So basically, as you toss in shifts for technicians you can play around with the minimum error. The code I'm using is an attempt to find the "Most ideal" schedule possible.
Basically the macro runs these steps:
1)Turns off Screenupdating and autocalculation
2)Copies a range that starts the model to a basic starting point (all half hours covered, but has tons of overstaffing and understaffing to minimize)
3)Identifies the maximum number of call takers you want to schedule
4)Starts to loop
-Loop starts with the last call taker slot
-For each technician, loop through each shift, for 21 combinations of workdays
-After each loop, calculate the error and compare it to the smallest error found so far
Here is the code (a simplified version) of what I've got so far.
Code:
Sub OptimizeMe()
'Declarations
Dim TechNum, j, n As Integer
Dim StartVal1, StartVal2, StartVal3, StartVal4, StartVal5, StartVal6, StartVal7 As Integer
Dim BestFit1, BestFit2, BestFit3, BestFit4, BestFit5, BestFit6, BestFit7 As Integer
Dim StartErr, CurrErr1 As Double
Application.ScreenUpdating = False
Sheets("SchedulingOptimization").Activate
Application.Calculation = xlCalculationManual
'Start default values
Range("DG5:HB11").Copy
Range("c5").PasteSpecial (xlPasteValues)
'Define Number of Techs (Range E2 contains the maximum number of techs
'_to schedule to shorten the number of unnecessary loops)
TechNum = Range("E2").Value + 2
'Remove the Extra Schedules
Cells(5, 102 - TechNum).Value = 1
Cells(5, 102 - TechNum).Copy
Range(Cells(5, 102 - TechNum), Cells(11, 102)).PasteSpecial (xlPasteValues)
Range("A1").Activate
'Begin Optimizing
For j = TechNum To 3 Step -1
'---Check if removing the tech will improve the optimization (is it a better default)
Calculate
StartErr = Range("DA357").Value
StartVal1 = Cells(5, j).Value
StartVal2 = Cells(6, j).Value
StartVal3 = Cells(7, j).Value
StartVal4 = Cells(8, j).Value
StartVal5 = Cells(9, j).Value
StartVal6 = Cells(10, j).Value
StartVal7 = Cells(11, j).Value
Cells(5, j).Value = 1
Cells(6, j).Value = 1
Cells(7, j).Value = 1
Cells(8, j).Value = 1
Cells(9, j).Value = 1
Cells(10, j).Value = 1
Cells(11, j).Value = 1
Calculate
CurrErr1 = Range("DA357").Value
If StartErr < CurrErr1 Then
Cells(5, j).Value = StartVal1
Cells(6, j).Value = StartVal2
Cells(7, j).Value = StartVal3
Cells(8, j).Value = StartVal4
Cells(9, j).Value = StartVal5
Cells(10, j).Value = StartVal6
Cells(11, j).Value = StartVal7
End If
'Start checking schedules (there are 21 combinations of "N" Schedules per technician) --FOR 2 DAYS OFF
For n = 145 To 1 Step -1
'don't loop through these shifts (I don't have 10 hour shifts ready yet)
If n < 50 Or n > 97 Then
'---Define Start Values
Calculate
CurrErr1 = Range("DA357").Value
BestFit1 = Cells(5, j).Value
BestFit2 = Cells(6, j).Value
BestFit3 = Cells(7, j).Value
BestFit4 = Cells(8, j).Value
BestFit5 = Cells(9, j).Value
BestFit6 = Cells(10, j).Value
BestFit7 = Cells(11, j).Value
'Combo 1 - Regular Weekdays (Sat/Sun off)
Cells(5, j).Value = 1
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = 1
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = 1
BestFit2 = n
BestFit3 = n
BestFit4 = n
BestFit5 = n
BestFit6 = n
BestFit7 = 1
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 2 - (Sun/Mon off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = 1
Cells(6, j).Value = 1
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = 1
BestFit2 = 1
BestFit3 = n
BestFit4 = n
BestFit5 = n
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 3 - (Mon/Tue off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = 1
Cells(7, j).Value = 1
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = 1
BestFit3 = 1
BestFit4 = n
BestFit5 = n
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 4 - (Tue/Wed off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = 1
Cells(8, j).Value = 1
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = 1
BestFit4 = 1
BestFit5 = n
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 5 - (Wed/Thur off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = 1
Cells(9, j).Value = 1
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = n
BestFit4 = 1
BestFit5 = 1
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 6 - (Thur/Fri off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = 1
Cells(10, j).Value = 1
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = n
BestFit4 = n
BestFit5 = 1
BestFit6 = 1
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 7 - (Fri/Sat off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = 1
Cells(11, j).Value = 1
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = n
BestFit4 = n
BestFit5 = n
BestFit6 = 1
BestFit7 = 1
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 8 - (Sun/Tue off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = 1
Cells(6, j).Value = n
Cells(7, j).Value = 1
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = 1
BestFit2 = n
BestFit3 = 1
BestFit4 = n
BestFit5 = n
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 9 - (Sun/Wed off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = 1
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = 1
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = 1
BestFit2 = n
BestFit3 = n
BestFit4 = 1
BestFit5 = n
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 10 - (Sun/Thur off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = 1
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = 1
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = 1
BestFit2 = n
BestFit3 = n
BestFit4 = n
BestFit5 = 1
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 11 - (Sun/Fri off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = 1
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = 1
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = 1
BestFit2 = n
BestFit3 = n
BestFit4 = n
BestFit5 = n
BestFit6 = 1
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 12 - (Mon/Wed off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = 1
Cells(7, j).Value = n
Cells(8, j).Value = 1
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = 1
BestFit3 = n
BestFit4 = 1
BestFit5 = n
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 13 - (Mon/Thur off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = 1
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = 1
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = 1
BestFit3 = n
BestFit4 = n
BestFit5 = 1
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 14 - (Mon/Fri off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = 1
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = 1
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = 1
BestFit3 = n
BestFit4 = n
BestFit5 = n
BestFit6 = 1
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 15 - (Mon/Sat off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = 1
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = 1
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = 1
BestFit3 = n
BestFit4 = n
BestFit5 = n
BestFit6 = n
BestFit7 = 1
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 16 - (Tues/Thurs off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = 1
Cells(8, j).Value = n
Cells(9, j).Value = 1
Cells(10, j).Value = n
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = 1
BestFit4 = n
BestFit5 = 1
BestFit6 = n
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 17 - (Tues/Fri off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = 1
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = 1
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = 1
BestFit4 = n
BestFit5 = n
BestFit6 = 1
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 18 - (Tues/Sat off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = 1
Cells(8, j).Value = n
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = 1
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = 1
BestFit4 = n
BestFit5 = n
BestFit6 = n
BestFit7 = 1
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 19 - (Wed/Fri off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = 1
Cells(9, j).Value = n
Cells(10, j).Value = 1
Cells(11, j).Value = n
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = n
BestFit4 = 1
BestFit5 = n
BestFit6 = 1
BestFit7 = n
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 20 - (Wed/Sat off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = 1
Cells(9, j).Value = n
Cells(10, j).Value = n
Cells(11, j).Value = 1
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = n
BestFit4 = 1
BestFit5 = n
BestFit6 = n
BestFit7 = 1
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
'Combo 21 - (Thur/Sat off)
Calculate
CurrErr1 = Range("DA357").Value
Cells(5, j).Value = n
Cells(6, j).Value = n
Cells(7, j).Value = n
Cells(8, j).Value = n
Cells(9, j).Value = 1
Cells(10, j).Value = n
Cells(11, j).Value = 1
Calculate
If CurrErr1 > Range("DA357").Value Then
BestFit1 = n
BestFit2 = n
BestFit3 = n
BestFit4 = n
BestFit5 = 1
BestFit6 = n
BestFit7 = 1
End If
Cells(5, j).Value = BestFit1
Cells(6, j).Value = BestFit2
Cells(7, j).Value = BestFit3
Cells(8, j).Value = BestFit4
Cells(9, j).Value = BestFit5
Cells(10, j).Value = BestFit6
Cells(11, j).Value = BestFit7
End If 'This endif goes with the "Not ready for 10-hour shifts" statement above
Next n
Calculate
Select Case TechNum - j + 1
Case 10, 20, 30, 40, 45, 48
MsgBox ("Done with Iteration: " & (TechNum - j + 1))
End Select
Next j
Range("DA357").Select
MsgBox ("Done!")
End SubThat's basically it - I'm not sure what do do from here to shorten the macro's run time.
Any suggestions?
(Also keep in mind this is a simplified version - I'm actually going to try to toss in 10 hour days, split shifts (yech), and so on. I've looked a little into the Solver - but I'm not sure if that's the way to go).
Anyway - I appreciate any assistance you can offer!
Adam
