VISHAL120
04-04-2016, 11:25 AM
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:
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: