none
Sum values RRS feed

  • Question

  • Hello,

    Are trying to sum values in a customized text field of a summary task sub tasks based on the wbs code and then write them to the summary task text23 field. So in the case below the values in the column next to 16.1 and 16.2 should be summarized to the column next to 16. The problem that occurs is that when checking the wbs code VBA does not work as the filter function in Project so when looping through the wbs code checking if the wbs code is more than 16 and less than 17 the code just continues to run on 17.1, 17.2 etc and add these values as well.

    In the case below the sum of 251571 + 1149263 should be summarized to 1400834 and written to the cell next to 16

    Any assistance would be appreciated!

    Thanks //Anders


    • Edited by AT_lamnea Thursday, October 16, 2014 6:17 AM
    Thursday, October 16, 2014 6:06 AM

Answers

  • Anders,

    Instead of reading the WBS code, try reading the outline level.

    A few months back I wrote a macro that does something similar to what you want. Here's the code for reference.

    'This macro averages values in the Number1 field for non-milestone tasks
    '   and rolls up and writes the value to each summary level
    Sub ResNamesRollup()
    Dim t As Task
    Dim St As Task
    Dim SumVal As Single, TSum As Single
    Dim Div As Integer, TDiv As Integer, MaxOL As Integer, i As Integer
    'find lowest outline level used in this file
    MaxOL = 0
    For Each t In ActiveProject.Tasks
        If MaxOL < t.OutlineLevel Then MaxOL = t.OutlineLevel
    Next t
    'calculate values starting with summaries at lowest level,
    '   step up through each outline level from MaxOL (lowest) to 1 (highest)
    '   Note: since loop is at summary level, lowest summary level is MaxOL -1
    TSum = 0: TDiv = 0
    For i = MaxOL - 1 To 1 Step -1
        For Each t In ActiveProject.Tasks
            If t.Summary And t.OutlineLevel = i Then
                For Each St In t.OutlineChildren
                    If Not St.Milestone Then
                        SumVal = SumVal + St.Number1
                        Div = Div + 1
                    End If
                Next St
                t.Number1 = SumVal / Div
                'sum up level 1 calculated values
                If i = 1 Then
                    TSum = TSum + t.Number1
                    TDiv = TDiv + 1
                End If
                SumVal = 0: Div = 0
            'pick up non-summary level 1 values
            ElseIf i = 1 And t.OutlineLevel = 1 And Not t.Milestone Then
                TSum = TSum + t.Number1
                TDiv = TDiv + 1
            End If
        Next t
    Next i
    ActiveProject.ProjectSummaryTask.Number1 = TSum / TDiv
            
    End Sub

    John


    • Edited by John - Project Thursday, October 16, 2014 9:25 PM code start
    • Marked as answer by AT_lamnea Tuesday, October 21, 2014 6:00 AM
    Thursday, October 16, 2014 8:12 PM

All replies

  • Hi,

    Although I'm not a developer, I'd suggest you to post your code if you're willing that someone spots some error in it. Some folks hanging around on those forums are impressive at VBA, they might help you out.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    Thursday, October 16, 2014 8:10 PM
    Moderator
  • Anders,

    Instead of reading the WBS code, try reading the outline level.

    A few months back I wrote a macro that does something similar to what you want. Here's the code for reference.

    'This macro averages values in the Number1 field for non-milestone tasks
    '   and rolls up and writes the value to each summary level
    Sub ResNamesRollup()
    Dim t As Task
    Dim St As Task
    Dim SumVal As Single, TSum As Single
    Dim Div As Integer, TDiv As Integer, MaxOL As Integer, i As Integer
    'find lowest outline level used in this file
    MaxOL = 0
    For Each t In ActiveProject.Tasks
        If MaxOL < t.OutlineLevel Then MaxOL = t.OutlineLevel
    Next t
    'calculate values starting with summaries at lowest level,
    '   step up through each outline level from MaxOL (lowest) to 1 (highest)
    '   Note: since loop is at summary level, lowest summary level is MaxOL -1
    TSum = 0: TDiv = 0
    For i = MaxOL - 1 To 1 Step -1
        For Each t In ActiveProject.Tasks
            If t.Summary And t.OutlineLevel = i Then
                For Each St In t.OutlineChildren
                    If Not St.Milestone Then
                        SumVal = SumVal + St.Number1
                        Div = Div + 1
                    End If
                Next St
                t.Number1 = SumVal / Div
                'sum up level 1 calculated values
                If i = 1 Then
                    TSum = TSum + t.Number1
                    TDiv = TDiv + 1
                End If
                SumVal = 0: Div = 0
            'pick up non-summary level 1 values
            ElseIf i = 1 And t.OutlineLevel = 1 And Not t.Milestone Then
                TSum = TSum + t.Number1
                TDiv = TDiv + 1
            End If
        Next t
    Next i
    ActiveProject.ProjectSummaryTask.Number1 = TSum / TDiv
            
    End Sub

    John


    • Edited by John - Project Thursday, October 16, 2014 9:25 PM code start
    • Marked as answer by AT_lamnea Tuesday, October 21, 2014 6:00 AM
    Thursday, October 16, 2014 8:12 PM
  • Thanks a lot John!

    Very helpful!

    Kind regards //Anders

    Tuesday, October 21, 2014 6:00 AM