Help for VBA Code as too much time while processing

[VISHAL120]

Hello Everybody ,

I am actually working on a planning project for textile which take into account to plan the whole factory operations. I am using the below codes to place the formula which are taking almost sometime 4 - 7 secs just for this calculation only which is too long as there are other calculation that also need to be done. And in total i have to wait for almost 15-20 secs if need to analyse a change in one row. Very time consuming in term of analysis.


As many of the calculation depends on the previous start dates and am not able to paste the formula as VALUE therefore leaving the formula on the sheet. And doing so the size of the file increases to 10 mb sometimes as the volume of data varies depending on the number of orders that need to be analyse.

the codes are as follows:


Sub Calculation_Start_Dates_End_Dates_System_Block_Dates_And_Main()
With Application
.Cursor = xlWait
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationManual

End With


starttime = Timer
Dim lLR As Long

Sheets("Shadow_Normal_Calc").Activate

With ThisWorkbook.Sheets("Shadow_Normal_Calc")
lLR = Cells(Rows.Count, "A").End(xlUp).Row
End With


'asigning the start date formula

With Range("AQ59:AQ" & lLR)
.Formula = "=IF(AF59>0,start_date_Plan($AY59:$EX59,shadow_Normal_Calc_Calendar_Row),"""")"
'.Value = .Value
End With
'Assigning the System Block Dates
With Range("AH59:AH" & lLR)
.Formula = "=IF(ISERROR(IF(OR(J59="""",K59="""",L59="""",M59=""""),"""",IF(W59=""CUT"",MAX(J59:M59),AQ58+2))),"""",IF(OR(J59="""",K59="""",L59="""",M59=""""),"""",IF(W59=""CUT"",MAX(J59:M59),AQ58+2)))"
'.Value = .Value
End With

'Assigning the MIN_MAX_DATES
With Range("AJ59:AJ" & lLR)
.Formula = "=IF(AI59="""",AH59,AI59)"
'.Value = .Value
End With

'assigning the main loading formula
With Range("AY59:EX" & lLR)
.Formula = "=IF(OR($AF59="""",$AF59="""",$AJ59=""""),0,IF(AY$57<$AJ59,0,IF(AY$57>=$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))))))"
.Value = .Value
End With

'assigning the Load control qty
With Range("AW59:AW" & lLR)
.Formula = "=AN59-SUM(AY59:EX59)"
.Value = .Value
End With


'Assigning the End Dates Formula
With Range("AR59:AR" & lLR)
.Formula = "=IF(AND(AF59>0,AW59<1,AQ59<>""""),end_date_Plan($AY59:$EX59,$AY$57:$EX$57),"""")"
.Value = .Value
End With





MsgBox Timer - starttime & " secs."


'Sheets("Shadow_Normal_Calc").Visible = False
With Application
.Cursor = xlDefault
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic

End With


End Sub

I will be most grateful if shown a better way of doing the formula or even coding it in pure VBA. As sometimes when i am placing the formula as VALUE many of the calculation are not occurring and sometimes having FALSE on the calculation due to dependencies of other column calculation to start the other calculation.

Am attaching a sample and part of the system of the file with the calculation for better understanding.

thanking you all in advance for helping in solving the problems as i have been searching different ways of doing it but could not figure out.

kind regards,

vishal :banghead:: pray2::crying:

[Bob Phillips]

What do we need to do to see the problem/time?

[VISHAL120]

Hi Xld,

thanks you for your kind reply.

In fact on the attached file I forgot to place the button to run the VBA code. I am therefore re-attaching the file again and uploading.

In fact the calculation work like below :

All operation starts with the Cut Dept and the other Dept will start 2 days after the start date of cutting and this continues for the other dept.

below an example:

Order Dept str DAte End Date
1001 CUT 04/04 11/05
1001 PRNT 06/04 13/05
1001 MEMB 08/04 15/05

Can you please download the sample file and execute the VBA code.

thanks for the help in advance.

regards,

Vishal

[VISHAL120]

Hi Gurus,

Sorry for asking again but am really needing help on the below and any help would be very appreciated.

thanking in advance for the kind help .