1 Attachment(s)
Advice needed to speed this code.
Hello Everybody,
Am actually using the below code to place formula for calculation in VBA. I need to fill it in almost 104 columns and down to rows till we have data right actually it is to 660 rows but later can grow further.
The actual time taken to complete the filling of the formula across the column and rows is taking a lot of time which is around from 55 sec to 68 secs just for this part and sometimes the screen get freeze and sometimes saying Excel Not responding.
Here is my code:
Code:
Sub Main_Normal_Calculation()
With Application
'.Cursor = xlWait
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
startTime = Timer
Dim lLR As Long
Call Block_date_Start_Date
With ThisWorkbook.Sheets("Shadow_Normal_Calc")
lLR = Cells(Rows.Count, "A").End(xlUp).Row
End With
'assigning the main loading formula
Application.StatusBar = " Automated Planning : Computing ...."
With Range("AY59:EX" & lLR)
.Formula = "=IF(OR($AF59="""",$AF59="""",$AJ59=""""),0,IF(AY$58<$AJ59,0,IF(AY$58>=$AJ59,IF(0<($AN59),MIN(($AN59-SUM(AX59:$AX59)),$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)-SUMIF($AM$58:$AM58,$AM59,AY$58))))))"
'.Application.Calculation = xlCalculationAutomatic
.Value = .Value
End With
'assigning the Load control qty
Application.StatusBar = " Automated Planning : Computing the Load Control qties"
With Range("AW59:AW" & lLR)
.Formula = "=AN59-SUM(AY59:EX59)"
'.Application.Calculation = xlCalculationAutomatic
.Value = .Value
End With
MsgBox Timer - startTime & " secs."
With Application
.Cursor = xlDefault
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
I would much appreciate if help on how i can speed this code please. I have been struggle for almost a month now but has not been able to figure it out.
Am attaching a sample of the file where you can see the calculation time taken by clicking on the buttons place on the sheet.
Thanking in advance for any help, advise and recommendations.
Vishal.:banghead:: pray2:
1 Attachment(s)
Advice needed to speed this code.
Hi Paul,
this is what am trying to figure out, how to do this on VBA instead of using the formula.
P45Cal: thanks again for the help. replying:
msg 3: yes it's an error we can remove one of the check on it.
Msg 6: No it shall not have duplicate as 1 row represent one dept therefore if one dept is declare it shall not be duplicated.
Msg 7: -SUMIF($AM$58:$AM65,$AM66,BE$58).
Normally this sumif is suppose to do the calculation based on the capacity set by dept from rows AV5:AV56. example.
if Cut Capacity is 6,500
then the calculation shall look like that:
|
|
|
|
|
Cut |
6500 |
6500 |
6500 |
6500 |
6500 |
6500 |
6500 |
order |
total mins |
Minimum mins |
block date |
still to load |
Date |
Mon-09-May |
Tue-10-May |
Wed-11-May |
Thu-12-May |
Fri-13-May |
Sat-14-May |
Mon-16-May |
1 |
15000 |
4000 |
10-May |
0 |
|
|
4000 |
4000 |
4000 |
3000 |
|
|
2 |
12000 |
3500 |
10-May |
0 |
|
|
2500 |
2500 |
2500 |
2500 |
2000 |
|
It shall check if there is still capacity to load and by how much like order 1 has already taken 4000 mins therefore the balance available mins is 2500 which is allocated to order 2 as even the Minimum mins to load is 3500 it can allocate only 2500 mins.
This is what this sumif is suppose to do about. Well I don't know if am doing it right that is why i need some advise and recommendations. Am attaching a file to illustrate an example of that.
I hope this can help you to figure out what the calculation is doing.
thanks sir for helping me for this problem.
vishal:banghead::banghead:
Advice needed to speed this code.
Hi,
Again thanks for the precious time and helpful hand.
The last corrected formula is this one:
IF(OR($AF60="",$AJ60=""),0,IF(AY$58<$AJ60,0,IF(AY$58>=$AJ60,IF(0<($AN60),MI N(($AN60-SUM(AX60:$AX60)),$AO60,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM60,AY$4:AY$56)-SUMIF($AM$58:$AM59,$AM60,AY$58:AY60))))))
The goal of this is to plan the whole factory starting from the cutting dept to finishing of the product.
1.Under the Factory there are different dept which are defined on range AV5 to AV52.
2.Normally the sequence of preceding for every order are different but all of them shall undergo to cutting, makeup .
3.Each Dept has to start 2 days after the start of the previous dept same is being checked by the routine Block_Date_Start_Date.
4.The Check for each dept and lines is down on column AM.
5.Only the Cutting Dept starts according to the max date achieved from Fabric date, Trims date,PPS,Pre Run and same is being checked by the routine Block_Date_Start_Date.
6.Each dept has a capacity allocated by day and we shall be planning according to the dept capacity until the capacity is fully loaded for that date and move to another day.
CUT |
1400 |
1400 |
1400 |
1400 |
1400 |
1400 |
1400 |
1400 |
1400 |
1400 |
1400 |
HEMB |
1000 |
1000 |
1000 |
1000 |
1000 |
1000 |
1000 |
1000 |
1000 |
1000 |
1000 |
MEMB |
1100 |
1100 |
1100 |
1100 |
1100 |
1100 |
1100 |
1100 |
1100 |
1100 |
1100 |
SPRAY |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
WASH |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
800 |
|
|
|
|
|
|
|
|
|
|
|
|
2_JOBURG |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
3_ROME |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
500 |
7.Also each order can have a Minimum Mins to be plan for the day as this depends upon the complexity of the specific order which is entered and decided during the planning. That is why we have a Min Mins Column which is column AO
Order |
Ordered qty |
Wip Qty |
Fabric date @ factory |
Trims @ factory |
PPS |
Pre Run |
Dept |
SYSTEM BLOCK DATE |
BLOCKED DATE MANUAL |
MAX MIN DATES |
mins Loaded |
Min Mins |
Str Date |
A |
3000 |
2000 |
4-Apr |
4-Apr |
10-Mar |
1-Apr |
CUT |
4-Apr |
|
4-Apr |
2000 |
1500 |
9-May |
A |
3000 |
1800 |
4-Apr |
4-Apr |
10-Mar |
1-Apr |
HEMB |
11-May |
|
11-May |
1800 |
500 |
11-May |
A |
3000 |
1600 |
4-Apr |
4-Apr |
10-Mar |
1-Apr |
MEMB |
13-May |
|
13-May |
1600 |
800 |
13-May |
A |
3000 |
700 |
4-Apr |
4-Apr |
10-Mar |
1-Apr |
2_JOBURG |
15-May |
|
15-May |
1600 |
400 |
16-May |
A |
3000 |
1200 |
4-Apr |
4-Apr |
10-Mar |
1-Apr |
WASH |
18-May |
|
18-May |
1200 |
600 |
18-May |
B |
1200 |
1000 |
5-Apr |
5-Apr |
5-Mar |
1-May |
CUT |
20-May |
|
20-May |
1000 |
900 |
10-May |
B |
1200 |
600 |
5-Apr |
5-Apr |
5-Mar |
1-May |
2_JOBURG |
12-May |
13-May |
13-May |
600 |
350 |
12-May |
B |
1200 |
400 |
5-Apr |
5-Apr |
5-Mar |
1-May |
WASH |
14-May |
|
14-May |
400 |
250 |
14-May |
As it can be seen here following the example:
Mon-09-May |
Tue-10-May |
Wed-11-May |
Thu-12-May |
Fri-13-May |
Sat-14-May |
Mon-16-May |
Wed-18-May |
Thu-19-May |
Fri-20-May |
Sat-21-May |
1400 |
600 |
|
|
|
|
|
|
|
|
|
|
|
500 |
500 |
500 |
300 |
|
|
|
|
|
|
|
|
|
800 |
800 |
200 |
|
|
|
|
|
|
|
|
|
|
400 |
400 |
400 |
400 |
|
|
|
|
|
|
|
|
600 |
600 |
|
|
|
800 |
100 |
|
|
|
|
|
|
|
|
|
|
|
|
350 |
250 |
|
|
|
|
|
|
|
|
|
|
250 |
150 |
|
|
|
|
8.Order A the sequence of loading is following the dept sequence that is CUT,HEMB,MEMB,2_JOBURG,WASH
9.So every dept is starting 2 after the previous dept even though we have capacity to load.
10.Order B is starting the CUT on 800 even the minimum is 900 because the remaining capacity to plan is 1400 - 600 which is 800. So even the min mins advised it shall not respect based on the remaining capacity as here shown.
IF(OR($AF60="",$AJ60=""),0,IF(AY$58<$AJ60,0,IF(AY$58>=$AJ60,IF(0<($AN60),MI N(($AN60-SUM(AX60:$AX60)),$AO60,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM60,AY$4:AY$56)-SUMIF($AM$58:$AM59,$AM60,AY$58:AY60))))))
11.Coming to the formula:
12.I have been able to build this formula based on several help receive from the internet and VBAEXPRESS
13.the IF(OR($AF60="",$AJ60=""),0 , Checks if the WIP is blank or the MIN MAX Column is blank then to place 0.
14.IF(AY$58<$AJ60,0 this part is suppose to check the start date from the calendar row (shadow_Normal_Calc_Calendar_Row),if the date is less than the date on the calendar then to place 0.
14.IF(AY$58>=$AJ60,IF(0<($AN60),MIN(($AN60-SUM(AX60:$AX60)),$AO60 : this suppose to take into consideration the min mins declare and to laod acccordingly until the full qty is loaded.
15.SUMIF(Shadow_Dept_Lines_Sum_Col,$AM60,AY$4:AY$56)-SUMIF($AM$58:$AM59,$AM60,AY$58:AY60): is suppose to check if the capacity hs been met and if so then use the the next column for calculation.
for info: this analysis take us almost 1 full day and with 5 people sitting together. As its very time consuming to do manually.
We have been able to compute it but this is the problem actually as for 1 change we have to wait for almost 2 mins as there are other calculation being done and this is the part taking much more time than expected.