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.

[SamT]

try this

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
     
    MsgBox "Block_date_Start_Date took " & Timer - startTime & " secs."
    startTime = Timer
    
     
    With ThisWorkbook.Sheets("Shadow_Normal_Calc")
        lLR = Cells(Rows.Count, "A").End(xlUp).Row
     
         'assigning the main loading formula
        'Application.StatusBar = " Automated Planning : Computing ...."
        Range("AY59").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))))))"
        With Range("AY59:EX" & lLR)
            .FillDown
            .Calculate
            .Formula = .Value
        End With
         
         'assigning the Load control qty
        'Application.StatusBar = " Automated Planning : Computing the Load Control qties"
        Range("AW59").Formula = "=AN59-SUM(AY59:EX59)"
        With Range("AW59:AW" & lLR)
            .FillDown
            .Calculate
            .Formula = .Value
        End With
    End With
     
    MsgBox "Main Code took " & Timer - startTime & " secs."
     
     
     
    With Application
        .Cursor = xlDefault
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
     
End Sub

[p45cal]

At the beginning of a formula you have:
.Formula = "=IF(OR($AF59="""",$AF59="""",$AJ59="""")
where $AF59 is checked twice; is this an unnecessary double check or should one of the $AF59s be different?

[Paul_Hossler]

Is it necessary to use formulas in the WS and then make them values?

A 'calculation' macro that only put the final results into the cells would be faster

[p45cal]

I notice also that row 59 is different from the ones below it in columns A:AR but your code puts the same formulae in row 59 in columns AY:EX as in the rows below.
Shouldn't the code only put formulae in from row 60 downwards rather then from row 59 downwards?

[p45cal]

In the range AV5:AV56 will there ever be duplicate values?
I ask because you've got a SUMIF in the formula (SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)) which currently only ever returns one value from the range rather than a sum of several values.

[p45cal]

There's something odd about your formula. In the code the end of the formula is -SUMIF($AM$58:$AM58,$AM59,AY$58)
In cell BE66 this results in: -SUMIF($AM$58:$AM65,$AM66,BE$58)
The third argument is usually a range that you want to sum, but at the moment it's only the date in row 58 above BE66:
Capture3.JPG
Surely not right?!

[p45cal]

psssst; if you or your company are going to use pirated versions of MS Office you would do well to hide it!

[VISHAL120]

Hi,Thanks SamT , i will try and revert if its ok with the timing. thanks you for your kind help.P45Cal: Yes you are right the formula shall start on row 60 and not on row 59. Well concerning pirate or not. I cannot tell you as its the administrator who do all in the installation and also my pc has just been bought. So am not in a situation to confirm if this is pirate or no.But still thanks for helping.

[p45cal]

Yes you are right the formula shall start on row 60 and not on row 59.

Now you have to tackle the other questions in msgs #3, #6 anf #7. I can get the time down to around 1 second I think, if I know the answers to these.

[VISHAL120]

Hi Paul,I did not get you well . can you please explain to me. As its been already month i am trying to figure out but is still block with that. So am ready to try any new advise.

[Paul_Hossler]

I was suggesting that instead of using the 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))))))"

if you could calculate the results using VBA and put the answer in the cells it might be faster and less round about than making values out of the formula

[VISHAL120]

Hi Paul,

this is what am trying to figure out, how to do this on VBA instead of using the formula.

P45Cal: thanks again for the help. replying:

msg 3: yes it's an error we can remove one of the check on it.

Msg 6: No it shall not have duplicate as 1 row represent one dept therefore if one dept is declare it shall not be duplicated.

Msg 7: -SUMIF($AM$58:$AM65,$AM66,BE$58).

Normally this sumif is suppose to do the calculation based on the capacity set by dept from rows AV5:AV56. example.

if Cut Capacity is 6,500

then the calculation shall look like that:

					Cut	6500	6500	6500	6500	6500	6500	6500
order	total mins	Minimum mins	block date	still to load	Date	Mon-09-May	Tue-10-May	Wed-11-May	Thu-12-May	Fri-13-May	Sat-14-May	Mon-16-May
1	15000	4000	10-May	0			4000	4000	4000	3000
2	12000	3500	10-May	0			2500	2500	2500	2500	2000

It shall check if there is still capacity to load and by how much like order 1 has already taken 4000 mins therefore the balance available mins is 2500 which is allocated to order 2 as even the Minimum mins to load is 3500 it can allocate only 2500 mins.


This is what this sumif is suppose to do about. Well I don't know if am doing it right that is why i need some advise and recommendations. Am attaching a file to illustrate an example of that.

I hope this can help you to figure out what the calculation is doing.

thanks sir for helping me for this problem.

vishal

[VISHAL120]

Hi SamT,

i have just tested the code it still running at 54 secs which is still time consuming. thank again for the help

[p45cal]

Msg 7: -SUMIF($AM$58:$AM65,$AM66,BE$58).
Normally this sumif is suppose to do the calculation based on the capacity set by dept from rows AV5:AV56. example.
It shall check if there is still capacity to load and by how much like order 1 has already taken 4000 mins therefore the balance available mins is…
This is what this sumif is suppose to do about. Well I don't know if am doing it right that is why i need some advise and recommendations. Am attaching a file to illustrate an example of that.
I hope this can help you to figure out what the calculation is doing.

OK, so it's just msg#7 that I'm not 100% sure about but I'm guessing that part is supposed to be (in cell BE66):
-SUMIF($AM$58:$AM65,$AM66,BE$58:BE65)

So unless I hear to the contrary I'll press on with that.

[VISHAL120]

Hi P45Cal,

Good morning. Yes you are right it shall be same as you mentioned for this sumif that is :-SUMIF($AM$58:$AM65,$AM66,BE$58:BE65).
My omission.

thank again for helping. And i hope that we can find the solution to speed up as actually its a seriou headache for still turning around 65- 70 secs.

thanking you in advance.

[p45cal]

Progress report: While I'm getting similar results with faster code, whether I use my code or your code I still need to run it several times to get stable results; there's some kind of circular reference in there and I'm trying to find it.

[VISHAL120]

Hi P45Cal,

Thank you in advance sir. i will wait for the end results for sure. As its a real problem for me right now and even for the other users also.

Again thank you for the kind help.

[offthelip]

I think I know why your code is so slow, the With range statement means the code accesses the spreadsheet multiple times, the way to do it is to write all the equations into an array and then write the array to the spreadsheet in one go . I just tired this code and it was a factor of 50 times faster than yours.

With ThisWorkbook.Sheets("Sheet1")
        lLR = Cells(Rows.Count, "A").End(xlUp).Row
    End With
     inarr = Range("AY59:EX" & lLR)
     '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
      endarray = 59 - lLR
      For i = 1 To endarray
        inarr(i, 1) = "=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))))))"
      Next i
       Range("AY59:EX" & lLR) = inarr

[SamT]

Not bad for a first time poster
Welcome aboard.