Advice needed to speed this code.

[VISHAL120]

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.