Forum Discussion
Changing cost rate by using macro
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
FillDown
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
- Uli,
You're welcome and thanks for the feedback. If I answered your question, please consider marking my response as the answer.
John
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 SubJohn
Hello,
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?
John
