Results 1 to 7 of 7

Thread: step cycle sum

  1. #1
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14

    step cycle sum

    Hi,

    I wrote this code to make money in a column.
    There are alternatives or corrections to the code to improve cycle times?

    Code:
    Option Explicit
    
    Sub Arr_ciclo_Column()
        Dim LastRow As Long
        Dim rng As Variant
        Dim DVal As Integer       'Ok
        DVal = Range("Q2").Value  '12
        LastRow = Range("E" & Rows.Count).End(xlUp).Row
        rng = Range("IV1:IV" & LastRow)   'Range Clear          rng = Redim...
        Dim Ciclo As Long
        For Ciclo = 3 To UBound(rng) Step DVal
            rng(Ciclo, 1) = Evaluate("SUM(E" & Ciclo & ":" & "E" & Ciclo + DVal - 1 & ")")
        Next
        Range("P1:P" & LastRow) = rng
    End Sub
    Lots of thanks in advance

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I would probably do it this way...
    Code:
    Sub Arr_ciclo_Column()
        Dim DVal As Long, Ciclo As Long
        DVal = Range("Q2").Value
        Application.ScreenUpdating = False
        For Ciclo = 3 To Range("E" & Rows.Count).End(xlUp).Row Step DVal
            Cells(Ciclo, "P").Value = WorksheetFunction.Sum(Cells(Ciclo, "E").Resize(DVal))
        Next
        Application.ScreenUpdating = True
    End Sub

  3. #3
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    Hi,

    It certainly would use if I were to enter a single value column.

    The complete cycle involves the following calculations for each step in the column:

    =SOMMA(E3:E14) 'Entry with the previous example
    =SOMMA(F3:F14)*-1
    =(L3/L4)
    '
    =SOMMA(E4:E15)
    =SOMMA(F4:F15)*-1
    =SOMMA(L7/L8)
    '
    =SOMMA(E5:E16)
    =SOMMA(F5:F16)*-1
    =SOMMA(L11/L12)
    '

    Completing the first cycle resume the calculation sequence
    I'm not sure what cycle is best to use in this case
    The paper uses 300.000 rows of data

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by PcMax View Post
    It certainly would use if I were to enter a single value column.

    The complete cycle involves the following calculations for each step in the column:

    =SOMMA(E3:E14) 'Entry with the previous example
    =SOMMA(F3:F14)*-1
    =(L3/L4)
    '
    =SOMMA(E4:E15)
    =SOMMA(F4:F15)*-1
    =SOMMA(L7/L8)
    '
    =SOMMA(E5:E16)
    =SOMMA(F5:F16)*-1
    =SOMMA(L11/L12)
    '

    Completing the first cycle resume the calculation sequence
    I'm not sure what cycle is best to use in this case
    The paper uses 300.000 rows of data
    I have no idea how the above flows from your first message. What is SOMMA... your local language name for the SUM function (seems unlikely since you used SUM in your first message)? What is a cycle? What is a complete cycle? What are you really trying to do? Your first message asked how the code you posted could be improved, nothing more, but your last message seems to be implying you asked something else instead (or at least it implies you think you told us more about the process than you actually did). I think you will need to provide more information before anyone will be able to help you further. Keep in mind when providing any information (and when asking future questions)... while everything about what you want to do is obvious to you, it is completely unknown to us... we only know what you tell us, so do not take for granted that we know or can deduce anything about your data, its layout or what you want to do with it.

  5. #5
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    Hi,

    I wrote Sum Somma and created confusion
    The language used in vba is standard while the formulas are in the local language.
    Now I attach a file in the hope of showing the correct indication of the request
    Attached Files Attached Files

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    may be..

    Code:
    Sub kTest()
        
        Dim r   As Long
        Dim c   As Long
        Dim i   As Long, j As Long
        Dim ka, k(), p
        
        r = Range("e" & Rows.Count).End(3).Row
        c = 12
        
        ka = Range("e3:f" & r)
        
        ReDim k(1 To UBound(ka, 1), 1 To 1)
        
        For i = 1 To UBound(ka, 1) Step c
            j = i
            For r = i To i + c - 1 Step c \ 3
                With Application
                    p = Evaluate("row(" & j & ":" & Application.Min(UBound(ka, 1), j + c - 1) & ")")
                    k(r, 1) = .Sum(.Index(ka, p, 1))
                    k(r + 1, 1) = .Sum(.Index(ka, p, 2)) * -1
                    k(r + 2, 1) = k(r, 1) / k(r + 1, 1)
                End With
                j = j + 1
            Next
        Next
        
        Range("q3").Resize(UBound(k, 1)) = k
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    Hi,

    Thank you so much for the solution.
    Will do the test with real data of the Sheet.

Similar Threads

  1. Click Run cycle
    By PcMax in forum Excel Help
    Replies: 6
    Last Post: 11-01-2011, 04:50 AM
  2. The Sum of A1 + A2 + A3 + A4 = 52
    By stanleydgromjr in forum Excel Help
    Replies: 3
    Last Post: 08-09-2011, 07:33 AM

Posting Permissions

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