In that case; if you want to sum left of cell in column5:
Code: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
Printable View
In that case; if you want to sum left of cell in column5:
Code: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
I just edited the OP's post #36 to Un-Tabluate the text from the tables.
The post is now readable.
@p45cal
This might improve speed too:
But you should realise that Evaluat is a very compactly written arrayformula. Arrayformulae do not speed up things in general.Code:If colm > 1 Then
sn= Evaluate("transpose(row(1:" & colm - 1 & "))")
cells(j,5) = Application.Sum(.Index(resultsAry, rw, sn))
End If
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.
All in acordance with what I wrote in #39.
Good that you tested it yourself.
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:It takes a considerably shorter time to produce the results.Code: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 = resultsAry
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
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.
That is just the type of thing I was referring to when I said:Quote:
Originally Posted by VISHAL120
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.Quote:
Originally Posted by p45cal
This sounds like a Worksheet Design problem.
Can you save the workbook as an Excel 2003 (xls) file and post it?
@SamT
Can you try to update your system to at least Office 2010 ?:devil2:
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.
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).Quote:
Originally Posted by VISHAL120
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).
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.Quote:
Can you try to update your system to at least Office 2010 ?:devil2:
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
Quote:
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.
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.
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.Quote:
Originally Posted by VISHAL120
I am having a conversation with snb.Quote:
So i want to know what you want to express by saying:
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
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.
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.
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.
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.
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.
that is why the data should be organized for Excel and not for people. Reports and result should be organized for people.Quote:
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.
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.
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.
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.
Hi,
can anyone please give a recommendation and advise please.
thanking in advance.
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.
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.