1 Attachment(s)
Advice needed to speed this code.
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:
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.:banghead:: pray2:
1 Attachment(s)
Advice needed to speed this code.
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:banghead::banghead: