separate material and work resources in two differents columns

Question

Hello everybody!

In the Gantt Chart view of tasks there is only one resources column that brings together work and material, however I would like to split the in two columns. Is there any way to separte them?

I've found a VBA subfunction on the internet that is supossed to split the resources in two different columns, however I have tried to run it and it does not compile. The function is as follows:

Sub CalcLaborMaterial()

Dim Job As Task

Dim Iemand As Resource

Dim WieDoenWat As Assignment

CustomFieldPropertiesEx FieldID:=pjCustomTaskCost1,

Attribute:=

For Each Job In ActiveProject.Tasks

If Not Job Is Nothing Then

Job.Cost1 = 0

Job.Cost2 = 0

End If

Next Job

For Each Job In ActiveProject.Tasks

If Not Job Is Nothing Then

Job.Cost1 = 0

Job.Cost2 = 0

For Each WieDoenWat In Job.Assignments

If WieDoenWat.ResourceType = pjResourceTypeWork Then

Job.Cost1 = Job.Cost1 + WieDoenWat.Cost

End If

If WieDoenWat.ResourceType = pjResourceTypeMaterial Then

Job.Cost2 = Job.Cost2 + WieDoenWat.Cost

End If

Next WieDoenWat

End If

Next Job

CustomFieldPropertiesEx FieldID:=pjCustomTaskCost1,

Attribute:=

End Sub

Actually I dont know if this is correct or if there is another way to do so. If anyone could help me with this issue I would really apreciate it! Thanks a lot in advance!

Answer 1

Irene,

Perhaps I misunderstood what you wanted. The macro simply separates the costs for work and material type resources and writes those costs into two separate field, Cost1 and Cost2. It does NOT change what you see in the Resource Names field. In order to see the results of the macro, you need to add the Cost1 and Cost2 fields as columns in the Gantt Chart view.

Does that help? If not, exactly what do you want?

John

Answer 2

Irene,

Yeah that macro has some strange/unnecessary stuff in it so I'm not surprised it didn't compile. Try this version.

Sub CalcLaborMaterial()

Dim Job As Task

Dim Iemand As Resource

Dim WieDoenWat As Assignment

For Each Job In ActiveProject.Tasks

    If Not Job Is Nothing Then

        Job.Cost1 = 0

        Job.Cost2 = 0

            For Each WieDoenWat In Job.Assignments

                If WieDoenWat.ResourceType = pjResourceTypeWork Then

                    Job.Cost1 = Job.Cost1 + WieDoenWat.Cost

                End If

                If WieDoenWat.ResourceType = pjResourceTypeMaterial Then

                    Job.Cost2 = Job.Cost2 + WieDoenWat.Cost

                End If

            Next WieDoenWat

    End If

Next Job

End Sub

Hope this helps.

John

Answer 3

Irene,

It is easy to modify the macro to show the resource names instead of resource costs in separate fields. Is that what you want? The modified macro below will put the work resource name(s) into Text1 and the material resource name(s) into Text2.

As far as seeing resources broken out separately, have you looked at the Task Usage view? It shows each task with resource assignments listed separately.

John

Sub LaborMaterial()

Dim Job As Task

Dim Iemand As Resource

Dim WieDoenWat As Assignment

For Each Job In ActiveProject.Tasks

    If Not Job Is Nothing Then

        Job.Text1 = "": Job.Text2 = ""

        For Each WieDoenWat In Job.Assignments

            If WieDoenWat.ResourceType = pjResourceTypeWork Then

                If Job.Text1 <> "" Then

                    Job.Text1 = Job.Text1 & ", " & WieDoenWat.ResourceName

                Else

                    Job.Text1 = WieDoenWat.ResourceName

                End If

            End If

            If WieDoenWat.ResourceType = pjResourceTypeMaterial Then

                If Job.Text2 <> "" Then

                    Job.Text2 = Job.Text2 & ", " & WieDoenWat.ResourceName

                Else

                    Job.Text2 = WieDoenWat.ResourceName

                End If

            End If

        Next WieDoenWat

    End If

Next Job

End Sub

Answer 4

Hello John, thanks again for you answer! As you said what I really need is to separate the name of the resources in two columns not the cost itself. I would like to see two Name fields: one with the name of work resources and the other one with material resources. As you said it seems that the VBA I found on the internet does not solve my problem. Do you know if there is any other function that shows work and material resources separately? Thanks a lot!

Answer 5

Hello John, thanks a lot for you help! I would like to ask you how to run the macro because now it compiles but it is no working since the resources are still in the same column. What I did is going to: tab developer--> Visual Basic, then I have created a new module , paste the function and ran it. Thanks in advance!