PDA

View Full Version : Advice needed to speed this code.



VISHAL120
05-14-2016, 03:15 AM
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.:banghead:: pray2:

SamT
05-14-2016, 09:02 AM
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
05-14-2016, 10:51 AM
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
05-14-2016, 11:03 AM
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
05-14-2016, 11:19 AM
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
05-14-2016, 12:16 PM
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
05-14-2016, 12:54 PM
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:
16183
Surely not right?!

p45cal
05-14-2016, 04:28 PM
psssst; if you or your company are going to use pirated versions of MS Office you would do well to hide it!

VISHAL120
05-16-2016, 04:48 AM
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
05-16-2016, 05:09 AM
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
05-16-2016, 05:10 AM
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
05-16-2016, 05:34 AM
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
05-16-2016, 09:15 AM
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:

VISHAL120
05-16-2016, 09:47 AM
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
05-16-2016, 03:11 PM
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
05-16-2016, 09:45 PM
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
05-17-2016, 05:40 PM
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
05-17-2016, 09:56 PM
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
05-20-2016, 09:22 AM
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
05-20-2016, 04:30 PM
Not bad for a first time poster :beerchug:
Welcome aboard.

offthelip
05-21-2016, 01:08 AM
Not bad for a first time poster :beerchug:
Welcome aboard.

Thanks for the comment, looking again , I realise that my solution is incomplete for what was required, however the basic principle of minimising the number of accesses to the spreadsheet is something I have used many times to speed up macros.

VISHAL120
05-21-2016, 02:14 AM
Hi ,

i have tried the above code but when running its placing the formula only on column AY and not on the Range("AY59:EX" & lLR) = inarr.

Can you please check and advise if this is the case as i have run it several times and it continue to place it on the column AY only.

thanks again for helping.

And i really need to figure out on this problem as we are suppose to present the planning to the department by next week but just because of the time its taking we are squeeze right now.

I would be very grateful for the help and recommendation on that,.

offthelip
05-21-2016, 02:38 AM
Hi,
apologies for my incomplete answer, but try this which uses the same technique but does a double loop


Sub test()
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
' column ex is column 154 column ay is column 59
endcol = 154 - 51

For i = 1 To endarray
For j = 1 To endcol
inarr(i, j) = "=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 j
Next i

Range("AY59:EX" & lLR) = inarr
End Sub

VISHAL120
05-21-2016, 02:45 AM
Hi,

Thanks for helping.

In fact i have just tested the code and i think its placing the formula only on the column AY and not on the :
Range("AY59:EX" & lLR) = inarr

can you please just confirm.

thanks again for helping.

offthelip
05-21-2016, 03:11 AM
Hi Vishal 120
I actually tested it this time, I changed the formula so that it made sense on my computer, but the principal is the same. There were a few coding errors ( looping round a negative index doesn't work) this code took 4 secs on my computer see how yours does:
(I put numbers from 1 to 445 in the rows A1 to A445)
Note the R1C1 reference in my equation is used to demonstrate changing the formula for each row (or column), I don't know whether your equation needs this but it is a useful technique to know about.



Sub test()
startTime = Timer
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 = lLR - 59
' column ex is column 154 column ay is column 59
endcol = 154 - 51
For i = 1 To endarray
For j = 1 To endcol
' inarr(i, j) = "=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))))))"
inarr(i, j) = "=sum(r1c1:r" & i & "c1)"


Next j
Next i
Range("AY59:EX" & lLR).Formula = inarr
MsgBox Timer - startTime & " secs."
End Sub

p45cal
05-21-2016, 04:13 AM
the With range statement means the code accesses the spreadsheet multiple timesThis is not true.


the basic principle of minimising the number of accesses to the spreadsheet is something I have used many times to speed up macros.That is true, however the original code gains access only once to the sheet with:

.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))))))"




This line:
Range("AY59:EX" & lLR).Formula = inarris no more efficient than this line:
.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))))))"




VISHAL120, I suspect strongly there is a circular reference on the sheet, only it takes some looking to find out where it is as Excel doesn't seem to report it (probably to do with the UDFs). I will look again when I get time (your formulae are many and long (and not always correct!)) and I'm not 100% sure what you intend with the formulae.

offthelip
05-21-2016, 04:24 AM
I have just compared this with your solution and I think mine is actually slower, so I don't think this is going to help

snb
05-21-2016, 04:56 AM
Never use Excelformulae in VBA
Never use UDF's in VBA

Do all the calculation in arrays


Sub Block_date_Start_Date()
sn = Sheet2.Cells(58, 1).CurrentRegion.Resize(, 156)

For j = 3 To UBound(sn)
If sn(j, 32) >= 0 Then
For jj = 51 To UBound(sn, 2)
If sn(j, jj) > 0 Then Exit For
Next
sn(j, 43) = sn(1, jj)
End If
Next

....

End Sub

p45cal
05-21-2016, 05:39 AM
Do all the calculation in arraysThis what I have so far done and get the same results as VISHAL120's formula but find that I have to run the code more than once to get the same result, hence my suspicion of circular refs. (cells in AY60:EX660 depend on cells outside that area, but some of those precedent cells in turn depend on values within AY60:EX660 - I've just got to find a chunk of time to find it/them).

Note also that cells in AY60:EX660 depend on cells within AY60:EX660 too (those in the same column above and in the same row to the left) and the order of my in-memory calculations takes account of that - perhaps Excel's on-sheet formulae calculate in a different order, but I doubt it.

A CLEAR explanation of what the relevant columns represent and what the output in AY60:EX660 is meant to represent from VISHAL120 would be helpful.

snb
05-21-2016, 05:53 AM
This what I have so far done.

I seem to be unable to find your code ???

p45cal
05-21-2016, 05:54 AM
I seem to be unable to find your code ???I haven't offered it yet!

p45cal
05-21-2016, 06:12 AM
for snb, my stage of development (you'll see frequent refs to 66, only because I was translating the code from a random single cell (BE66)!)
So far just to ensure I get the same results as VISHAL120's corrected formula (msg#15), then I'll try and make it faster/shorter/more elegant.
You'll see a reference to you, snb, in one of the comments!
Sub Main_Normal_Calculation2()
Dim lLR As Long

With Application
'.Cursor = xlWait
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
With ThisWorkbook.Sheets("Shadow_Normal_Calc")
lLR = .Cells(.Rows.Count, "A").End(xlUp).Row
FR = .Range("Shadow_Normal_Calc_Header_Row").Row + 1
AFary = .Range("AF" & FR & ":AF" & lLR).Value
AJary = .Range("AJ" & FR & ":AJ" & lLR).Value
AMary = .Range("AM" & FR & ":AM" & lLR).Value
ANary = .Range("AN" & FR & ":AN" & lLR).Value
AOary = .Range("AO" & FR & ":AO" & lLR).Value
BigAreaAboveAry = .Range("AY5:EX56").Value
DateRowAry = .Range("shadow_Normal_Calc_Calendar_Row").Value
ReDim resultsAry(1 To .Range("AY60:EX" & lLR).Rows.Count, 1 To .Range("AY60:EX" & lLR).Columns.Count)
ShadowDeptLinesSumCol = .Range("$AV$5:$AV$56").Value

For rw = 1 To UBound(resultsAry)
af66 = AFary(rw, 1)
an66 = ANary(rw, 1)
ao66 = AOary(rw, 1)
aj66 = AJary(rw, 1)
am66 = AMary(rw, 1)
For colm = 1 To UBound(resultsAry, 2)
Debug.Assert Not (rw = 2 And colm = 3)
'=IF(OR($AF66="",$AJ66=""),
If af66 = "" Or aj66 = "" Then
resultsAry(rw, colm) = 0
Else
'we have the equivalent of =IF(BE$58<$AJ66,0,IF(BE$58>=$AJ66,"srumpf")) where we're askibng 2x about the value of BE58 v AJ66 in 2 if statements, only need if then else:
be58 = DateRowAry(1, colm)
If be58 < aj66 Then 'put this in first IF?
resultsAry(rw, colm) = 0 'put this in first IF?
Else 'BE$58>=$AJ66 automatically unless strings involved.
'if(0<($AN66)...
If an66 > 0 Then
'the first argument in the MIN function:
'($AN66-SUM($AX66:BD66)) this will need values to the left to be calculated first!
mysum = 0
For i = 1 To colm - 1 'do this by slicing and dicing arrays a la snb instead? Test for speed.
mysum = mysum + resultsAry(rw, i)
Next i
MIN1 = an66 - mysum

'the 2nd argument in the MIN function $AO66:
'ao66

'the 3rd argument in the MIN function :
'SUMIF(Shadow_Dept_Lines_Sum_Col,$AM66,BE$4:BE$56)-SUMIF($AM$58:$AM65,$AM66,BE$58)
'=SUMIF(Shadow_Dept_Lines_Sum_Col,$AM66,BE$4:BE$56)
i = Application.Match(am66, ShadowDeptLinesSumCol, 0)
If IsError(i) Then MIN3a = Empty Else MIN3a = BigAreaAboveAry(i, colm)

'-SUMIF($AM$58:$AM65,$AM66,BE$58)
'awaiting explanation/correction from OP. Nonsense at the moment.It's always = 0.
MIN3b = 0
For i = 1 To rw - 1
If AMary(i, 1) = am66 Then
' Stop
MIN3b = MIN3b + resultsAry(i, colm)
End If
Next i

MIN3 = MIN3a - MIN3b 'awaiting clarification

resultsAry(rw, colm) = Application.Min(MIN1, ao66, MIN3)
Else
'there is no else in the worksheet formula.
End If
End If
End If
Next colm
Next rw
'Sheets("Sheet3").Cells(1, 1).Resize(rw, colm).Value = resultsAry
.Range("AY60").Resize(rw, colm).Value = resultsAry

'assigning the Load control qty
With .Range("AW59:AW" & lLR)
.Formula = "=AN59-SUM(AY59:EX59)"
'.Application.Calculation = xlCalculationAutomatic
'''.Value = .Value
End With
End With
Block_date_Start_Date 'p45cal moved

With Application
' .Cursor = xlDefault
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub

Paul_Hossler
05-21-2016, 06:33 AM
These are the two formulas from the OP's #1, but there have been several corrections to them

Does anyone have the current error-less versions?



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))))))"
.Value = .Value
End With

With Range("AW59:AW" & lLR)
.Formula = "=AN59-SUM(AY59:EX59)"
.Value = .Value
End With

p45cal
05-21-2016, 07:04 AM
These are the two formulas from the OP's #1, but there have been several corrections to them

Does anyone have the current error-less versions?For the first I think it's this and I think these two are the equivalent:
Range("AY60:EX660").FormulaR1C1 = "=IF(OR(RC32="""",RC36=""""),0,IF(R58C<RC36,0,IF(R58C>=RC36,IF(0<(RC40),MIN((RC40-SUM(RC50:RC[-1])),RC41,SUMIF(Shadow_Dept_Lines_Sum_Col,RC39,R4C:R56C)-SUMIF(R58C39:R[-1]C39,RC39,R58C:R[-1]C))))))"
Range("AY60:EX660").Formula = "=IF(OR($AF60="""",$AJ60=""""),0,IF(AY$58<$AJ60,0,IF(AY$58>=$AJ60,IF(0<($AN60),MIN(($AN60-SUM($AX60:AX60)),$AO60,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM60,AY$4:AY$56)-SUMIF($AM$58:$AM59,$AM60,AY$58:AY59))))))"

For the second, apart from it not starting at row 60 and thus being
With Range("AW60:AW" & lLR)
.Formula = "=AN60-SUM(AY60:EX60)"
.Value = .Value
End WithI haven't yet twigged what's wrong with it.

snb
05-21-2016, 12:11 PM
@p45cal

Ahhh, now I see !
Thank you.

I wish the OP could describe his/her goal.
The unraveling of clumsy formulae takes too much time.

VISHAL120
05-22-2016, 10:17 AM
Hi,

Again thanks for the precious time and helpful hand.

The last corrected formula is this one:
IF(OR($AF60="",$AJ60=""),0,IF(AY$58<$AJ60,0,IF(AY$58>=$AJ60,IF(0<($AN60),MIN(($AN60-SUM(AX60:$AX60)),$AO60,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM60,AY$4:AY$56)-SUMIF($AM$58:$AM59,$AM60,AY$58:AY60))))))


The goal of this is to plan the whole factory starting from the cutting dept to finishing of the product.
1.Under the Factory there are different dept which are defined on range AV5 to AV52.
2.Normally the sequence of preceding for every order are different but all of them shall undergo to cutting, makeup .
3.Each Dept has to start 2 days after the start of the previous dept same is being checked by the routine Block_Date_Start_Date.
4.The Check for each dept and lines is down on column AM.
5.Only the Cutting Dept starts according to the max date achieved from Fabric date, Trims date,PPS,Pre Run and same is being checked by the routine Block_Date_Start_Date.
6.Each dept has a capacity allocated by day and we shall be planning according to the dept capacity until the capacity is fully loaded for that date and move to another day.



CUT
1400
1400
1400
1400
1400
1400
1400
1400
1400
1400
1400


HEMB
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000


MEMB
1100
1100
1100
1100
1100
1100
1100
1100
1100
1100
1100


SPRAY
800
800
800
800
800
800
800
800
800
800
800


WASH
800
800
800
800
800
800
800
800
800
800
800
















2_JOBURG
500
500
500
500
500
500
500
500
500
500
500


3_ROME
500
500
500
500
500
500
500
500
500
500
500




7.Also each order can have a Minimum Mins to be plan for the day as this depends upon the complexity of the specific order which is entered and decided during the planning. That is why we have a Min Mins Column which is column AO



Order
Ordered qty
Wip Qty
Fabric date @ factory
Trims @ factory
PPS
Pre Run
Dept
SYSTEM BLOCK DATE
BLOCKED DATE MANUAL
MAX MIN DATES
mins Loaded
Min Mins
Str Date


A
3000
2000
4-Apr
4-Apr
10-Mar
1-Apr
CUT
4-Apr

4-Apr
2000
1500
9-May


A
3000
1800
4-Apr
4-Apr
10-Mar
1-Apr
HEMB
11-May

11-May
1800
500
11-May


A
3000
1600
4-Apr
4-Apr
10-Mar
1-Apr
MEMB
13-May

13-May
1600
800
13-May


A
3000
700
4-Apr
4-Apr
10-Mar
1-Apr
2_JOBURG
15-May

15-May
1600
400
16-May


A
3000
1200
4-Apr
4-Apr
10-Mar
1-Apr
WASH
18-May

18-May
1200
600
18-May


B
1200
1000
5-Apr
5-Apr
5-Mar
1-May
CUT
20-May

20-May
1000
900
10-May


B
1200
600
5-Apr
5-Apr
5-Mar
1-May
2_JOBURG
12-May
13-May
13-May
600
350
12-May


B
1200
400
5-Apr
5-Apr
5-Mar
1-May
WASH
14-May

14-May
400
250
14-May




As it can be seen here following the example:



Mon-09-May
Tue-10-May
Wed-11-May
Thu-12-May
Fri-13-May
Sat-14-May
Mon-16-May
Wed-18-May
Thu-19-May
Fri-20-May
Sat-21-May


1400
600













500
500
500
300











800
800
200












400
400
400
400










600
600





800
100














350
250












250
150







8.Order A the sequence of loading is following the dept sequence that is CUT,HEMB,MEMB,2_JOBURG,WASH
9.So every dept is starting 2 after the previous dept even though we have capacity to load.
10.Order B is starting the CUT on 800 even the minimum is 900 because the remaining capacity to plan is 1400 - 600 which is 800. So even the min mins advised it shall not respect based on the remaining capacity as here shown.
IF(OR($AF60="",$AJ60=""),0,IF(AY$58<$AJ60,0,IF(AY$58>=$AJ60,IF(0<($AN60),MIN(($AN60-SUM(AX60:$AX60)),$AO60,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM60,AY$4:AY$56)-SUMIF($AM$58:$AM59,$AM60,AY$58:AY60))))))
11.Coming to the formula:
12.I have been able to build this formula based on several help receive from the internet and VBAEXPRESS
13.the IF(OR($AF60="",$AJ60=""),0 , Checks if the WIP is blank or the MIN MAX Column is blank then to place 0.
14.IF(AY$58<$AJ60,0 this part is suppose to check the start date from the calendar row (shadow_Normal_Calc_Calendar_Row),if the date is less than the date on the calendar then to place 0.
14.IF(AY$58>=$AJ60,IF(0<($AN60),MIN(($AN60-SUM(AX60:$AX60)),$AO60 : this suppose to take into consideration the min mins declare and to laod acccordingly until the full qty is loaded.
15.SUMIF(Shadow_Dept_Lines_Sum_Col,$AM60,AY$4:AY$56)-SUMIF($AM$58:$AM59,$AM60,AY$58:AY60): is suppose to check if the capacity hs been met and if so then use the the next column for calculation.

for info: this analysis take us almost 1 full day and with 5 people sitting together. As its very time consuming to do manually.

We have been able to compute it but this is the problem actually as for 1 change we have to wait for almost 2 mins as there are other calculation being done and this is the part taking much more time than expected.

VISHAL120
05-22-2016, 10:31 AM
if its not well visible thanks to inform me i will attached it on a file.

p45cal
05-22-2016, 10:48 AM
To snb
I'm replacing:
mysum = 0
For i = 1 To colm - 1 'do this by slicing and dicing arrays a la snb instead.
mysum = mysum + resultsAry(rw, i)
Next iwith:
mysum = 0
If colm > 1 Then
With Application
mysum = .Sum(.Index(resultsAry, rw, .Transpose(Evaluate("row(1:" & colm - 1 & ")"))))
End With
End If
Both seem to give the same results.
2 questions:
1. Is there a slicker way?
2. So far it seems a lot slower - is this your experience?

snb
05-22-2016, 12:38 PM
@p45cal;

Not using Excel functions in VBA seems to be always faster.

Since you sum all values per 'row' you could try to reduce the code to:


If colm > 1 Then
With Application
mysum = .Sum(.Index(resultsAry, rw))
End With
End If

Basically:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion

For j = 1 To UBound(sn)
MsgBox Application.Sum(Application.Index(sn, j))
Next
End Sub

p45cal
05-22-2016, 01:37 PM
Since you sum all values per 'row' you could try to reduce the code to:Not quite, it sums the sheet-equivalent of only the cells to the left on the same row as the cell in question, but not including that cell's value.

snb
05-22-2016, 02:05 PM
In that case; if you want to sum left of cell in column5:


Sub M_snb()
sn = Cells(1).CurrentRegion

For j = 2 To UBound(sn)
sn(j, 5) = Evaluate(Split(Join(Application.Index(sn, j), "+"), "+" & sn(j, 5))(0))
Next
End Sub

SamT
05-22-2016, 04:51 PM
I just edited the OP's post #36 to Un-Tabluate the text from the tables.

The post is now readable.

snb
05-23-2016, 12:07 AM
@p45cal

This might improve speed too:


If colm > 1 Then
sn= Evaluate("transpose(row(1:" & colm - 1 & "))")

cells(j,5) = Application.Sum(.Index(resultsAry, rw, sn))
End If

But you should realise that Evaluat is a very compactly written arrayformula. Arrayformulae do not speed up things in general.

p45cal
05-23-2016, 09:19 AM
snb, after testing of several of the above one-liners, I've reverted to my original looping code as it's still more then 10 times as fast.

snb
05-23-2016, 09:22 AM
All in acordance with what I wrote in #39.

Good that you tested it yourself.

p45cal
05-23-2016, 11:06 AM
VISHAL120, I've spent as much time on this as I'm going to spend.
I think the end of your final formula should be:
…-SUMIF($AM$58:$AM59,$AM60,AY$58:AY59))))))


It's taking me too long to work out how calling Block_date_Start_Date interferes, as when this is included (the comment apostrophe removed) we need several iterations (more than 6) for the results to become stable (but still there remain some 15 cells with different results).
In the attached is some code which I'm 99.5% sure is the equivalent of this long formula; this part:
With ThisWorkbook.Sheets("Shadow_Normal_Calc")
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
FR = .Range("Shadow_Normal_Calc_Header_Row").Row + 1
WIPAry = .Range("AF" & FR & ":AF" & LR).Value 'col AF
MaxMinDateAry = .Range("AJ" & FR & ":AJ" & LR).Value 'col AJ
LineDeptAry = .Range("AM" & FR & ":AM" & LR).Value 'col AM
MinsLoadedAry = .Range("AN" & FR & ":AN" & LR).Value 'col AN
MinMinsAry = .Range("AO" & FR & ":AO" & LR).Value 'col AO
CapacityAry = .Range("AY5:EX56").Value
DateRowAry = .Range("shadow_Normal_Calc_Calendar_Row").Value
ReDim resultsAry(1 To .Range("AY60:EX" & LR).Rows.Count, 1 To .Range("AY60:EX" & LR).Columns.Count)
ShadowDeptLinesSumCol = .Range("$AV$5:$AV$56").Value

For rw = 1 To UBound(resultsAry)
If rw Mod 10 = 0 Then Application.StatusBar = rw 'keeps the user informed of progress.
WIP = WIPAry(rw, 1)
MinsLoaded = MinsLoadedAry(rw, 1)
MinMins = MinMinsAry(rw, 1)
MaxMindate = MaxMinDateAry(rw, 1)
LineDept = LineDeptAry(rw, 1)

For colm = 1 To UBound(resultsAry, 2)
ResultsColumnDate = DateRowAry(1, colm)
If WIP = "" Or MaxMindate = "" Or ResultsColumnDate < MaxMindate Then
resultsAry(rw, colm) = 0
Else
If MinsLoaded > 0 Then
AllPreviousDaysMins = 0
For i = 1 To colm - 1
AllPreviousDaysMins = AllPreviousDaysMins + resultsAry(rw, i)
Next i
RemainingMins = MinsLoaded - AllPreviousDaysMins

i = Application.Match(LineDept, ShadowDeptLinesSumCol, 0)
If IsError(i) Then Capacity = Empty Else Capacity = CapacityAry(i, colm)

SameDayAndLineDeptCapacityAlreadyUsed = 0
For i = 1 To rw - 1
If LineDeptAry(i, 1) = LineDept Then
SameDayAndLineDeptCapacityAlreadyUsed = SameDayAndLineDeptCapacityAlreadyUsed + resultsAry(i, colm)
End If
Next i
RemainingCapacity = Capacity - SameDayAndLineDeptCapacityAlreadyUsed
resultsAry(rw, colm) = Application.Min(RemainingMins, MinMins, RemainingCapacity)
Else
'there is no 'else' in the original formula.
End If
End If
Next colm
Next rw
.Range("AY60").Resize(rw - 1, colm - 1).Value = resultsAryIt takes a considerably shorter time to produce the results.

I've altered your Function Start_Date_Plan to make it less convoluted and faster.

In the attached are a couple of sheets you should delete: Start which is a copy of the values returned by your formula (after correction). Sheet3 just contains formulae to compare newer results with Start, with the count of different cells in cell DB1 and any dissimilar cells highlighted in red.

Whether you use formulae or code to get these values I doubt very much you can rely upon them until you sort out these circular references. As a 'for-instance', using your attached file in msg#1, if you delete part of the results area, say cells AY60:BK75, you'll need to click your button 3 or 4 times, each with its attendant wait, before the results are stable and the cells in AW60:AW75 are all zero. I'm guessing it's likely to need many more button-clicks if the whole grid was cleared.

If calculations are mostly being done in-memory, there's little advantage to turning screen-updating and auto-calculation off, then on again.

Investigation shows that most of the time taken by your code isn't actually placing the formula in the cells - that takes place in a fraction of a second - most of the time is taken calculating the cells' formulae. Excel will calculate the results of a formula placed in a cell by vba immediately, whether or not calculation is set to manual.

I'd be interested to know how fast the code above is on your computer.

The file was too big to attach (extra check sheets) so it's here: https://app.box.com/s/j34teqs8eptgk8v48f13pkdbiyymk0wc

VISHAL120
05-24-2016, 05:05 AM
Hi P45Cal,

thanks so much for your time and patience.
I have run the code and if not removed the actual data calculation the time is 4.05 secs.

But when i have clear all the calculated data i have had to click almost 5 times to have the required results and with a total time of 15.53 secs. Can you please advise as normally with each change down on quantities or minimum qty on an order we will need to clear the previous data before previewing the newly calculated data.

is there a possibility that it do the calculation on one click as to understand if all calculation has been done we will need to check all the orders is are well loaded.

thanks for your suggestion and thank you again for the great help.

p45cal
05-24-2016, 05:53 AM
But when i have clear all the calculated data i have had to click almost 5 times to have the required results and with a total time of 15.53 secs. Can you please advise as normally with each change down on quantities or minimum qty on an order we will need to clear the previous data before previewing the newly calculated data.

is there a possibility that it do the calculation on one click as to understand if all calculation has been done we will need to check all the orders is are well loaded.

That is just the type of thing I was referring to when I said:
It's taking me too long to work out how calling Block_date_Start_Date interferes, as when this is included (the comment apostrophe removed) we need several iterations (more than 6) for the results to become stable (but still there remain some 15 cells with different results).The problem is that some of the cells needed to calculate the main part of the grid are themselves dependant on what's in the grid. You need to make sure that all cells which are used in calculating the main grid are NOT dependant on any of the cells in that main grid.

SamT
05-24-2016, 06:13 AM
This sounds like a Worksheet Design problem.

Can you save the workbook as an Excel 2003 (xls) file and post it?

snb
05-24-2016, 06:34 AM
@SamT

Can you try to update your system to at least Office 2010 ?:devil2:

VISHAL120
05-25-2016, 03:26 AM
Hi everybody,

I am unable to attach the file right now i will attach it tonight for version in 2003.
I am actually using excel 2010 and 2013.

concerning the dependency of the calculation within the grid, its difficult to remove as it shall take into consideration what qty has been planned previously and calculating the remaining qty to planned. That is why it will depend on the previous value calculated on the grid. I have try to figure out it this can be done in another method but still has not been able to.

example if we have 2,500 mins in total to plan with a min minutes per day of 450 it will be calculated as follow :

day1 day2 day3 day4 day5 day6
450 450 450 450 450 250.

Thus it will always check the cumulative planned previously to plan the qty remaining.
thanks again for helping and to recommend new possibilities if possible.

p45cal
05-25-2016, 04:11 AM
concerning the dependency of the calculation within the grid, its difficult to remove as it shall take into consideration what qty has been planned previously and calculating the remaining qty to planned. That is why it will depend on the previous value calculated on the grid. I have try to figure out it this can be done in another method but still has not been able to.

example if we have 2,500 mins in total to plan with a min minutes per day of 450 it will be calculated as follow :

day1 day2 day3 day4 day5 day6
450 450 450 450 450 250.

Thus it will always check the cumulative planned previously to plan the qty remaining.
It is all right for the formula to check on other cells within the grid (it does this for previously calculated cells to the left and above a given cell in the grid).
It is not OK for the cells in the main grid to use cells outside the grid, which in turn refer to cells within the grid in their formulae.

You need to check the formulae in the cells outside the grid which your original formula referred to and:
1) See if they can get their data from outside the grid
2) Or see if they're needed at all in the original formula, or whether the equivalent info can be gained from elsewhere (outside the grid).

SamT
05-25-2016, 07:32 AM
Can you try to update your system to at least Office 2010 ?:devil2:

I am saving to buy a hard drive so I can update my system to a different computer. A month after that, I can buy a used Office 2007 dvd.

VISHAL120
05-25-2016, 10:00 AM
SamT,

I did not understand your message can you be more explicit on what you want to tell.

In my Office am using 2010 and home am on 2013 is there any problem for that.

Please i respect all people on the forum and i value the people,their time, their patience and recommendations. Especially those who are helping us to grow in our knowledge and work. Frankly speaking this forum has help me solve many of our repetitive work that were done by people manually for which i always give credit to the person helping and to the forum in concern.

So i want to know what you want to express by saying:

SAMT

I am saving to buy a hard drive so I can update my system to a different computer. A month after that, I can buy a used Office 2007 dvd.

VISHAL120
05-25-2016, 10:34 AM
Hi P45Cal,

Thank you again for your precious time.

The parameters that it need outside the calculation grid are :
1. the starting date for the next dept which is dependent from the previous dept or if their is a date set manually.
2. the total mins to plan and the minimum mins capacity for the day.

These are the info that it required from outside and which are prerequisite for the calculation to run. The way You have proceeded its very helpful and quicker. we need only to find out the several click to complete the whole calculation as before that their are other calculation that need to be done before coming two this one. I have been trying to wrap it all but it shows still problem as we need to check the through the 600 rows if all has been plan or not.

p45cal
05-25-2016, 12:08 PM
the starting date for the next dept which is dependent from the previous dept or if their is a date set manually.Give me an example, somewhere in the middle of the grid, of a dept, then show me all the cells involved for all 3 sections in blue in the quote above.

SamT
05-25-2016, 06:49 PM
So i want to know what you want to express by saying:
I am having a conversation with snb.

VISHAL120
05-25-2016, 11:09 PM
Hi P45Cal,

Please find attached an example as requested. It starts from Row 77 to 82 which shows an order with its complete dept from Cut to Finish.
I have also place some text messages for each of the column to show which column and row are dependant to each other and all the column which are dependent i have coloured it yellow as pattern and blue for the fonts which will easily identify and to read.

I have also leave to formula for these rows so that we can see the calculation on the grid and the column used if we try to edit the formula. This will be easier to understand also i guess.

thanks .

vishal

SamT
05-26-2016, 04:57 PM
I think that you are trying to eat the entire Elephant at one meal.


How do you eat an elephant? One small meal at a time.


There are things that Excel can do much better than people and there are things that people can do much better than Excel.

I think that you are trying to make Excel work like your people have worked for so long that you are much better at it than Excel can be at doing it the way your people do it.

More later.

VISHAL120
05-27-2016, 01:47 AM
SamT ,

thanks for your comments for the elephant.

for your information this was running well when we planning on 100 rows before with formula. But now its not possible as we need to plan uo to 1,500 rows due to the factory capacity which has increase and that is why i am opting to do it with VBA.

Also in textile we always has dept to start based on previous dept date and for me this what we call planning in our sector and if this is the case do you think we shall put the date manually for it to calculate. Its impossible then in that case you are asking me to make the planning sit 3 hours with 10 people together to be able to give a good planning to production people.

I understand it complex and difficult that is why i need help on it.

Do you have a better recommendation of doing it.

SamT
05-27-2016, 09:40 AM
One large problem with your approach is that everything is on one worksheet. Excel, and VBA work best if different types or groups of data are on different worksheets.



Arrange the basic systemic data so it is very easy for Excel. (systemic = Products and factories.) Each System Unit gets its own sheet

Design various User Friendly worksheets to present details and summaries to people. Each such Report sheet is reasonably specialized.
Use VBA and Excel to fill out those Report sheets

Create a VBA "Orders" UserForm for people to input Order details and a sheet to list those values in an Excel friendly format.
Design Factory Staus sheets to list Factory Status in Excel friendly format.

Use VBA and Excel to analyze Factory status and Order details.
Create a VBA Userform "Assignment Orders" to present the result of the analysis to people so they can assign Orders to a select Factory.



At this time, with little knowledge of actual procedures, I am leaning towards a standardized Factory Class module and VBA code that uses Factory Class objects. Possibly a standardized Order Class Module.

See attached for Excel Friendly Systemic data sheets. The layout is critical, only the top two rows are formatted in any way, an empty column is required between different groups of data types. "LONDON" and "ENJOAR" are examples of possible data sheets for factories and Products.

Most Systemic data sheets can be hidden from the Users.

The Factory details are included on Product sheets because Product lines change much more often than Factory details.

The actual details on each example are limited to what I could find on your last attachment.

VISHAL120
06-01-2016, 10:39 AM
Hi ,

Sorry for the late reply.

thanks for the proposal SamT. In fact the sheet that i have send is the sheet where the calculation is being done. The end presentation to the user is very much simpler and very user friendly . As before the calculation is ready we need to check each parameters date like the fabric, Ok prod, trims date, etc. and also to calculate the mins based on the efficiency and the minimum qty set per day and its just after all these calculations are done the all data are sent to this sheet for the main planning events to occur.

After that all these calculated data are sent back the end user sheets to show how each order and there dept has been planned and if all dates are on target and if not its highlight the dept so that the user can easily see the orders which are in danger of delivery.

All the other parts of the calculation takes almost 10 - 12 secs to occur and the only one that take a lot of time is the one that i have sent which takes almost 52 -65 secs.

So for every analysis that a user will do like changing the minimum qty per day or changing the efficiency of the specific dept he will have to wait 12+ 65 = 77 secs to see the analysis results which is very time consuming.

if you want i can send the complete file but due to the size and seeing where i am having the problem that is why i have send only the part that am really block.

SamT
06-01-2016, 12:27 PM
I enjoy that kind of Project. Unfortunately my main computer is broken and this little laptop just is not good enough to work on a large project.

I would like to see the entire file, but I would only want to see the data and calculations for 3 orders and 3 factories.


So for every analysis that a user will do like changing the minimum qty per day or changing the efficiency of the specific dept he will have to wait 12+ 65 = 77 secs to see the analysis results which is very time consuming.that is why the data should be organized for Excel and not for people. Reports and result should be organized for people.

Excel must look at the entire sheet and calculate everything on the sheet. If you have a sheet for each factory, only that sheet and the results Report sheet need to be calculated.

In programming your project is known as 'organic,' meaning that it started small and manageable, but over time more and more was added to it until now it is no longer manageable.

In my experienced opinion, it is time to step back and look at what you need, without considering what you have, then design a Project from the ground up to accomplish that in an efficient manner.

VISHAL120
06-01-2016, 02:18 PM
Hi SamT,

thanks for your reply.

I will remove all the other orders from it, will place 3-4 orders with its dept and lines ( factory) and make the changes as you request and will post the file latest by tomorrow. As you say its really a big project and making a planning for a whole factory and lines at the same time for the each dept. its taken us almost a year to come what we are today and for sure it will be a great help for anyone who work in textile sector.

its not a problem for me after the completion to post it in the forum for free so that other of our friends can use it if it help them.

thanks again.

VISHAL120
06-02-2016, 01:13 PM
Hi SamT,

Please find the link below for the complete project.

https://app.box.com/s/ryqk6taz3n1aa3lwd6hyy72dycysoeb7

be careful when opening and enabling as it try to add ins as a toolbar to run it. otherwise we can still deactivate the toolbar by going though the macro and run the clear toolbar .

We need to click on the plan Factory for it to run. And there are other fields where we can adjust the capacity.

if you need further information please do inform. will be glad to give and of course suggestion will be most welcome.

VISHAL120
06-07-2016, 09:41 AM
Hi,

can anyone please give a recommendation and advise please.

thanking in advance.

p45cal
06-10-2016, 07:09 AM
After examining the attachment in msg#58, whenever I've had the inclination to try and sort this problem out, it has been with some trepidation, since it is complex.
There is at least one effective circular reference in the sheet which needs more iterations than you'd expect for the values in the grid to become stable.
It does need a radical rethink in the approach to solving this - where you start with the raw data, make calculations on it, make calculations on those results and finally populate the grid. At the moment, values in the grid are used to populate cells outside the grid, which are then used to populate the grid again. Trying to do this in your workbook will be time-consuming. I'm not going to do it, especially after seeing that enormous workbook in msg# 65! I can't unravel the logic, and I don't know your business.

A pointer: if you were able to calculate one row at a time in that sheet, so that all the values in that row (in and out of the grid) were calcualated without circular references and remained set in concrete once calculated then you should be able to get quick (<5 seconds) results after each alteration of raw data. Try to re-arrange the columns on the sheet so that any cell's formula (or calculation through vba) refers only to cells on rows above and/or cells in columns to the left of that cell, no referring to cells to the right on the same row, and no referring to cells on rows below at all. This should guarantee the absence of circular references. Then it should be possible to convert to vba to reduce file size and cut calculation time.

VISHAL120
06-11-2016, 11:27 AM
Hi P45CAL,

thanks for the advise i will try examining the code you give me as option and combine the proposal as you mentioned. One thing is that if we do not want to use the column outside the grid which is the Block start date which fetch the date of the start date of the previous dept from the grid and to add the constant date defined by user from another sheet which is ( Start_Next_Department_Days), can we make it fetch the date from the grid instead then it will more independent from the column outside the grid for the start of the next dept..

by the way for the time being i have had to decrease the number of columns from the grid which previous was 107 columns on the grid and now it have modified it to 75 columns but still its using alomost 55 secs for the whole calculation.

i will still try to find out the best way out by searching all the way possible. again thanks for helping.