Sep 15 2021 09:14 AM
Sep 15 2021 09:14 AM
In a project I need to calculate both the sales and the costs incurred. I have solved this using different cost rates (A and B). Now I am looking for a way to quickly change the cost rate from A to B and vice versa using a macro?
I have the following macro, but I always get an error message:
' Makro Kostensatz_nach_A_ändern
ViewApplyEx Name:="Vorgang: Einsatz", ApplyTo:=0
SelectTaskField Row:=2, Column:="Kostensatztabelle"
SetResourceField Field:="Kostensatztabelle", Value:="A"
SelectTaskField Row:=0, Column:="Kostensatztabelle", Height:=23
ViewApplyEx Name:="&Gantt-Diagramm", ApplyTo:=0
Since this is an English language forum, I am attaching two pictures of the cost rate table. I think then my problem will become clear.
Thanks for your help
Sep 15 2021 10:34 AM - edited Sep 15 2021 01:37 PM
First of all even though VBA code elements are all in English (by default), please understand that any reference to views or fields (i.e. string value) must be in the language being used, in your case, German. Perhaps you already know that.
It would have been helpful to know what error message you received but on further inspection of your macro code, I see you are attempting to set a resource field in a task based view (i.e. Task Usage). That probably explains the error you see.
However, it isn't quite clear exactly what you are trying to do. Based on the statement that selects a task field with a height of 23 leads me to believe you are trying to change the assignment cost rate tables for all the assignments in the whole plan or only for a given task. But, perhaps not.
The pictures you attached don't really tell anything other than there are separate cost rates for the "A" and "B" cost rate tables. A picture or screen shot of the Task Usage view and a more detailed explanation of what you want to do will help me understand your intent so I can offer assistance.
Nonetheless, anticipating what your response to the above will be, here is a macro that will convert costs for all resource assignments in a plan using either Cost Rate Table "A" or Cost Rate Table "B", based on user input. I even converted the messages to German :)
'macro by John - Project , 9/15/21
Dim t As Task
Dim a As Assignment
Dim Val As Single
Val = InputBox(Prompt:="Kosten- oder Verkaufswerte anzeigen?" & vbCr _
& "Geben Sie „0“ für Kosten oder „1“ für Verkäufe ein", Title:="Kostensatz-Umrechner")
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
For Each a In t.Assignments
a.CostRateTable = Val
Sep 16 2021 12:42 AM
The task is as follows:
I have material (material 1 and 2) that I sell at the price of X € /unit and buy at the price of Y € /unit.
I have stored the sales price in the Ressource: Tabelle as cost rate A (Kostensatz A), the purchase price as cost rate B (Kostensatz B).
In the Gantt chart, I have assigned the material to the tasks (Task 1 and 2). In the cost column (Kosten), I now see the values of the sales price (default in project to access cost rate A).
I have added the Kostensatztabelle (Cost Rate Table) column to the Vorgang: Einsatz (Task: Usage) view (screenshot).
The macro should now do the following: It should change all values in the column Kostensatztabelle (Cost rate table) from A to B in the view Vorgang: Einsatz (Task:Usage), so that I can see the purchase costs in the Gantt chart. It works manually.
I hope it is a little bit clearer and you understand my problem.
Sep 16 2021 08:19 AM
Sep 18 2021 08:24 AMSolution