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:
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.