Evaluate("SUM

Hi,

With this code I get the expected result ...

Code:

---

Sub Test_A()
    Dim Riga As Long
    Dim Dn As Long
    Dim rng As Variant
    Dim Num As Integer
    Num = Cells(2, 12)              'Value = 10
    Dn = Range("C" & Rows.Count).End(xlUp).Row
    rng = Range("IV1:IV" & Dn)      'Range without any data
    For Riga = Num + 1 To UBound(rng)
        rng(Riga, 1) = Evaluate("SUM(C" & Riga - Num + 1 & ":" & "C" & Riga & ")/L2")  '=SUM(C2:C11)/L2
    Next
    Range("O1:O" & Dn) = rng
End Sub

---

I used an array to avoid delays with 500,000 rows to update.
Definitely worth a general overhaul to improve the code.

Can you provide a written description and/or provide a before and after view of the worksheet (or both) so that we have some idea what this code is doing? I have a gut feeling just looking at the shape of the code that it can be made more efficient, but without a clearer understanding of what the code is attempting to do for you, I won't know for sure.

Hi,

I'm working with data from a list.
The calculations are based on a calculation of personal mobile media.
Seeking advice to improve and correct parts of the code.

(The code I wrote is the result of my own interpretation)

Code:

---

Sub snb()
  sn = Sheets(1).Columns(3).SpecialCells(2)

  For j = 11 To UBound(sn)
    sn(j, 1) = (sn(j, 1) + sn(j - 1, 1) + sn(j - 2, 1) + sn(j - 3, 1) + sn(j - 4, 1) + sn(j - 5, 1) + sn(j - 6, 1) + sn(j - 7, 1) + sn(j - 8, 1) + sn(j - 9, 1)) / 10
  Next

  Cells(1, 15).Resize(UBound(sn)) = sn
  Cells(2, 15).Resize(9).ClearContents
End Sub

---

Hi,

Did you mean like this:

Code:

---

Sub snb()
  Sn = Sheets(1).Columns(3).SpecialCells(2)
  Sn1 = Sn
  For j = 11 To UBound(Sn)
    Sn1(j, 1) = (Sn(j, 1) + Sn(j - 1, 1) + Sn(j - 2, 1) + Sn(j - 3, 1) + Sn(j - 4, 1) + Sn(j - 5, 1) + Sn(j - 6, 1) + Sn(j - 7, 1) + Sn(j - 8, 1) + Sn(j - 9, 1)) / 10
  Next
  Cells(1, 15).Resize(UBound(Sn)) = Sn1
  Cells(2, 15).Resize(9).ClearContents
End Sub

---

Keep in mind that the value 10 in range L2 I can edit ( 1, 2,,,10, 11, 12,,, ecc)

This code seems to produce the same answer as the code you posted...

Code:

---

Sub Test_B()
  Dim LastRow As Long
  LastRow = Range("C" & Rows.Count).End(xlUp).Row
  With Range("O" & Range("L2").Value + 1 & ":O" & LastRow)
    .Formula = "=SUM(OFFSET(C$1:C$" & Range("L2").Value & ",ROW(A1),0))/L$2"
    .Value = .Value
  End With
End Sub

---

Hi,

Quote:

---

This code seems to produce the same answer as the code you posted...

---

Code:

---

Sub Test_C()
  Dim LastRow As Long
  Dim MediaL2 As Long
  MediaL2 = Range("L2").Value
  LastRow = Range("C" & Rows.Count).End(xlUp).Row
  With Range("O" & MediaL2 + 1 & ":O" & LastRow)
    .Formula = "=SUM(OFFSET(C$1:C$" & MediaL2 & ",ROW(A1),0))/" & MediaL2
    .Value = .Value
  End With
  Cells(1, 15).Resize(MediaL2).ClearContents
End Sub

---

I only had to add the last line to delete the values ​​of a previous increase when the value of L2.
Thanks for the directions suggested
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=176255#p176255
https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Quote:

---

Originally Posted by PcMax

Hi,

Code:

---

Sub Test_C()
  Dim LastRow As Long
  Dim MediaL2 As Long
  MediaL2 = Range("L2").Value
  LastRow = Range("C" & Rows.Count).End(xlUp).Row
  With Range("O" & MediaL2 + 1 & ":O" & LastRow)
    .Formula = "=SUM(OFFSET(C$1:C$" & MediaL2 & ",ROW(A1),0))/" & MediaL2
    .Value = .Value
  End With
  Cells(1, 15).Resize(MediaL2).ClearContents
End Sub

---

I only had to add the last line to delete the values ​​of a previous increase when the value of L2.
Thanks for the directions suggested

---

If I understand the purpose of that last line (highlighted in red) correctly, shouldn't it appear at the top of the code rather than the bottom? Located at the bottom, there are circumstances when it would clear values that were just placed, right?

Hi,

Quote:

---

If I understand the purpose of that last line (highlighted in red) correctly, shouldn't it appear at the top of the code rather than the bottom? Located at the bottom, there are circumstances when it would clear values that were just placed, right?

---

In my tests I'm not deleting data you just entered, to be sure the rule is to place the first cycle:

Code:

---

Cells(1, 15).Resize(MediaL2).ClearContents

---

Thank you for the valuable suggestions

Quote:

---

Originally Posted by PcMax

In my tests I'm not deleting data you just entered, to be sure the rule is to place the first cycle:

Code:

---

Cells(1, 15).Resize(MediaL2).ClearContents

---

---

Nevermind... I had misunderstood your intent when I first read your added code line... I see what you are doing now. I am glad everything is working for you now.