Advice needed to speed this code.

[VISHAL120]

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.