VBA Help with Summary Tasks

Question

I am trying to write VBA to sum children tasks if the summary task is 0.  If it is not 0, then do nothing:

If(Expression, True, False)

If(Summary Task = 0,Summary Task  = Sum all children, keep it what it is)

Answer 1

cwilkins59,

I recall reading your post in the main newsgroup but I didn't contribute to the thread and didn't really pay much attention to the specifics. For reference could you expand a bit on your request. Which field has the data? What do you want to do with the summed up child values?

VBA code to sum up the child tasks is pretty trivial if just one level of indentation is involved. It gets more complex to sum up each level of a multi-level hierarchy. What is your file structure?

John

Answer 2

It will be a cost field using Cost1.  I want the summed up child values to go into the Summary task as long as the current value in the summary is 0.  If there is a value at the summary task already, then I want it to do nothing.  There wouldn't be a set number of what levels it would need to sum - it would just depend on that specific summary task as the heirarchy is random and deep in some cases.

 

Answer 3

swilkins59,

Okay but what I need to know is this. Let's say your schedule has 4 levels of hierarchy. And let's say the level 4 summary line cost1 value is zero. You want to sum up the subtasks under that summary to the summary line. Do you also need that summed up value to continue summing up to the level 3, 2, 1 summary lines (i.e. adding to the subtask cost1 values at each summary level)? Or, is each summary level independent (i.e. subtasks only sum up to their parent summary line and no further)?

The sum up process can be done with all VBA or a combination of a very simple VBA macro and the use of another extra cost field (e.g. Cost2). Are you willing to use another extra cost field (it would have a formula)?

John

Answer 4

Hi,

may be this macro can be helpful.

http://parthrawal.blogspot.com/2011/05/how-to-roll-up-cost-at-summary-task.html

Hope this helps.

---

Regards, Sujit - MSTC

Answer 5

Sujit,

I think the original poster got frustrated and gave up. I was ready to help him out but he never clarified exactly what he wanted. I suspect he wanted to sum up cost1 values at all summary levels if there was no data at the summary line but maybe he did want to sum up values to the immediate summary level only.

The macro you posted on your blog will sum up values to the immediate summary level but it will not sum up values to all summary levels. I assume it was your intent to sum up to the immediate level only. I did notice an error in the declaration statements. In Project cost values are not integer and therefore as written your macro will fail with a runtime error of "overflow". I suggest you declare cst and cst1 as Single or Double.

Hope this helps.

John

Answer 6

Thanks Will do the same :)

---

Regards, Sujit - MCTS

Answer 7

Sujit,

You're welcome.

John