Evaluate("SUM

**PcMax** · 09-09-2012, 11:32 PM

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.

**Rick Rothstein** · 09-10-2012, 12:15 AM

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.

**PcMax** · 09-10-2012, 01:13 AM

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)

**snb** · 09-10-2012, 02:45 AM

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

**PcMax** · 09-10-2012, 03:02 AM

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)

**Rick Rothstein** · 09-10-2012, 04:38 AM

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

**PcMax** · 09-10-2012, 10:12 AM

Hi,

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

**Rick Rothstein** · 09-10-2012, 11:17 AM

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?

**PcMax** · 09-10-2012, 04:00 PM

Hi,

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

**Rick Rothstein** · 09-10-2012, 09:52 PM

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.