Results 1 to 10 of 10

Thread: Evaluate("SUM

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

    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.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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.

  3. #3
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    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)
    Attached Files Attached Files

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    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

  5. #5
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    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)

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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

  7. #7
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    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
    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
    Last edited by DocAElstein; 05-08-2024 at 02:29 AM.

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by PcMax View Post
    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?

  9. #9
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    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

  10. #10
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by PcMax View Post
    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.

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Reversing a "First Middle Last" Name to "Last, First Middle" Name Format
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-06-2014, 10:04 PM
  3. Replies: 5
    Last Post: 04-18-2013, 02:30 AM
  4. Follow-up to "Excel Number Format: Indian Style Comma Separation"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 04-14-2012, 10:46 PM
  5. Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 03-20-2012, 03:46 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
  •