Consulting

Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 41 to 60 of 68

Thread: Advice needed to speed this code.

  1. #41
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  2. #42
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I just edited the OP's post #36 to Un-Tabluate the text from the tables.

    The post is now readable.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #43
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @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.

  4. #44
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.

  5. #45
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    All in acordance with what I wrote in #39.

    Good that you tested it yourself.

  6. #46
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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 = resultsAry
    It 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
    p45cal
    Everyone: 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.

  7. #47
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    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.

  8. #48
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by VISHAL120 View Post
    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:
    Quote Originally Posted by p45cal View Post
    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.
    p45cal
    Everyone: 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.

  9. #49
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    This sounds like a Worksheet Design problem.

    Can you save the workbook as an Excel 2003 (xls) file and post it?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #50
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @SamT

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

  11. #51
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location

    advice needed to speed this code

    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.

  12. #52
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by VISHAL120 View Post
    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).
    Last edited by p45cal; 05-25-2016 at 04:23 AM.
    p45cal
    Everyone: 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.

  13. #53
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Can you try to update your system to at least Office 2010 ?
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  14. #54
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    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.

  15. #55
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    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.

  16. #56
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by VISHAL120 View Post
    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.
    p45cal
    Everyone: 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.

  17. #57
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    So i want to know what you want to express by saying:
    I am having a conversation with snb.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  18. #58
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location

    advice needed to speed this code

    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
    Attached Files Attached Files

  19. #59
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  20. #60
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •