Consulting

Results 1 to 4 of 4

Thread: SUM for a dynamic table

  1. #1
    VBAX Regular
    Joined
    Jan 2017
    Location
    Warsaw
    Posts
    70
    Location

    SUM for a dynamic table

    Hey guys!

    I'm working currently on a function for a growing table.
    The current structure is following this approach:
    Unbenannt.PNG

    The first column consists the number for the Person; Column B the Month und C the respective Value.

    The aim is to sum the months, if the cumulated value is greater than (>=) 15.
    So the first person has only a cumulated value of 2 > no sum or call
    The second person (16) has a cumulated value of 15 > sum (1)
    So the month September get the value of +1 (in a matrix with a months and the value/ sum)
    The third person has an value 15 at and 20.

    The problem is that I need only the sum of the months, if the value is >=15
    Then I need the sum, if the value is growing up to 20.
    The steps a defined by 5 units.
    So the first sum is called by 15 and the second sum by 20 and so on.
    The table ist growing. So the person gets by the next update maybe a new row and the cumulated value ist growing with every update.


    Do you understand this approach/ problem?
    Or do you need more information?



    Best regards!

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    Try this, you haven't siad where you want the results so a have output it to columns j and K

    Sub test()
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(Cells(1, 1), Cells(lastrow, 4))
    Dim Monarr(1 To 12, 1 To 2) As Variant
    Monarr(1, 1) = "January"
    Monarr(2, 1) = "February"
    Monarr(3, 1) = "March"
    Monarr(4, 1) = "April"
    Monarr(5, 1) = "May"
    Monarr(6, 1) = "June"
    Monarr(7, 1) = "July"
    Monarr(8, 1) = "August"
    Monarr(9, 1) = "September"
    Monarr(10, 1) = "October"
    Monarr(11, 1) = "November"
    Monarr(12, 1) = "December"
    For k = 1 To 12
     Monarr(k, 2) = 0
    Next k
    
    
    cnt = 0
    cntlim = 15
    person = 0
    For i = 2 To lastrow
       If inarr(i, 1) <> person Then
                  ' new person
            cnt = 0
            cntlim = 15
            person = inarr(i, 1)
        End If
                 
         cnt = cnt + inarr(i, 3)
         If cnt >= cntlim Then
                 cntlim = cntlim + 5
                 For k = 1 To 12
                  If inarr(i, 2) = Monarr(k, 1) Then
                   Monarr(k, 2) = Monarr(k, 2) + 1
                  End If
                 Next k
         End If
     Next i
    Range(Cells(1, 10), Cells(12, 11)) = Monarr
    
    
     
     
     
     End Sub

  3. #3
    VBAX Regular
    Joined
    Jan 2017
    Location
    Warsaw
    Posts
    70
    Location
    Thanks a lot!
    I thought it would maybe work with excel functions, but this works too!

    Is your concept also as a function (own vba function) possible?

  4. #4
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    You can't have one function that writes the outputs for all months because a spreadsheet function can only write into one cell, however the code is easily modified so that you can enter the month as a parameter and it will add the sum for that month see below:

    In the spreadsheet put
    =testfunction(J1)
    where J1 holds the text for the month concerned ( I used the cell that my subroutine wrote into)

    then the function is:
    Function testfunction(Mnth As Range)
    allcnt = 0
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(Cells(1, 1), Cells(lastrow, 4))
    
    
    cnt = 0
    cntlim = 15
    person = 0
    For i = 2 To lastrow
       If inarr(i, 1) <> person Then
                  ' new person
            cnt = 0
            cntlim = 15
            person = inarr(i, 1)
        End If
                 
         cnt = cnt + inarr(i, 3)
         If cnt >= cntlim Then
                 cntlim = cntlim + 5
                
                  If inarr(i, 2) = Mnth Then
                   allcnt = allcnt + 1
                  End If
                
         End If
     Next i
    testfunction = allcnt
     
     
     
     End Function
    If you then copy this function down you will get the other months ( assuming the text is in column j)

Posting Permissions

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