step cycle sum

[PcMax]

Hi,

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


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

[Rick Rothstein]

I would probably do it this way...

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

[PcMax]

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

[Rick Rothstein]

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.

[PcMax]

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

[Admin]

Hi

may be..


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

[PcMax]

Hi,

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