Changing cost rate by using macro

Copper Contributor

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:


Sub Kostensatz_nach_A_ändern()
' 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
End Sub


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


7 Replies


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

Sub CostRateChg()
'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
Next a
End If
Next t
End Sub






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.



Okay, that helps. Did you try the macro code I posted? Does it do what you need and if not, what should be changed?
Hi, sorry, but I cannot find the macro. Where did you post it?
It's part of the response I posted on Sept 15, part of the last paragraph.


John, you are brilliant! This is exactly what I need! The macro runs great! Many, many thanks and have a great weekend. Be back next time, I'm sure I'll have many more questions.

Love from Germany

best response confirmed by Uli60 (Copper Contributor)
You're welcome and thanks for the feedback. If I answered your question, please consider marking my response as the answer.
1 best response

Accepted Solutions
best response confirmed by Uli60 (Copper Contributor)
You're welcome and thanks for the feedback. If I answered your question, please consider marking my response as the answer.

View solution in original post