# Sum values

• ### 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 Thursday, October 16, 2014 6:17 AM
Thursday, October 16, 2014 6:06 AM

• Anders,

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 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
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
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

End Sub

John

• Edited by Thursday, October 16, 2014 9:25 PM code start
• Marked as answer by 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
• Anders,

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 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
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
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

End Sub

John

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