Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 68

Thread: Advice needed to speed this code.

  1. #21
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    Quote Originally Posted by SamT View Post
    Not bad for a first time poster
    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.

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

  3. #23
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

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

  5. #25
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

  6. #26
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by offthelip View Post
    the With range statement means the code accesses the spreadsheet multiple times
    This is not true.

    Quote Originally Posted by offthelip View Post
    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 = inarr
    is 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.
    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. #27
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

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

  9. #29
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by snb View Post
    Do all the calculation in arrays
    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
    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.

  10. #30
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Quote Originally Posted by p45cal View Post
    This what I have so far done.
    I seem to be unable to find your code ???

  11. #31
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by snb View Post
    I seem to be unable to find your code ???
    I haven't offered it yet!
    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.

  12. #32
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    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. #33
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    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) / 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

  14. #34
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Paul_Hossler View Post
    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<($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 being
    With Range("AW60:AW" & lLR) 
        .Formula = "=AN60-SUM(AY60:EX60)" 
        .Value = .Value 
    End With
    I haven't yet twigged what's wrong with it.
    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.

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

  16. #36
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location

    Advice needed to speed this code.

    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

  17. #37
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    if its not well visible thanks to inform me i will attached it on a file.

  18. #38
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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 i
    with:
                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?
    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.

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

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

Posting Permissions

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