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,.
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
Last edited by SamT; 05-21-2016 at 08:02 AM. Reason: Added CODE Tags with Editor's # icon. Added white space
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.
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
Last edited by SamT; 05-21-2016 at 08:05 AM. Reason: Added CODE Tags with Editor's # icon
This is not true.
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:is no more efficient than this line:Range("AY59:EX" & lLR).Formula = inarr.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.
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
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
This 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.
Last edited by p45cal; 05-21-2016 at 05:53 AM.
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
Paul
------------------------------------------------------------------------------------------------------------------------
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
(multiple files can be selected while holding Ctrl key) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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<($AN 60),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 beingI haven't yet twigged what's wrong with it.With Range("AW60:AW" & lLR) .Formula = "=AN60-SUM(AY60:EX60)" .Value = .Value End With
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
@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.
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),MI N(($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),MI N(($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.
Last edited by SamT; 05-22-2016 at 04:49 PM. Reason: Un-Tabulated the text. Hope I put the tables in the right places
if its not well visible thanks to inform me i will attached it on a file.
To snb
I'm replacing:with: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 iBoth seem to give the same results.mysum = 0 If colm > 1 Then With Application mysum = .Sum(.Index(resultsAry, rw, .Transpose(Evaluate("row(1:" & colm - 1 & ")")))) End With End If
2 questions:
1. Is there a slicker way?
2. So far it seems a lot slower - is this your experience?
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
@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:
Basically:If colm > 1 Then With Application mysum = .Sum(.Index(resultsAry, rw)) End With End If
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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.