Material usage based on formula

Darren101

Okay, here ya go. There are actually two macros. Run the first (SetUp_Paint_List) to set up the Task Text1 value list so you can select which material resource (paint) you want for the task. Run the second (MatlCost) to calculate how much paint will be needed based on your input of area in the Task Number1 field and coverage data entered in the Resource Number1 field. It will then calculate the total cost.

John

Option Explicit
'This procedure populates a value list with all material resources in file
Sub Setup_Paint_List()
Dim r As Resource
'First kill value list if it already exists (provides or real time changes to material list)
CustomFieldDelete Field:=pjTaskText1
'create value list with material resources
CustomFieldPropertiesEx FieldID:=pjCustomTaskText1, attribute:=pjFieldAttributeValueList
For Each r In ActiveProject.Resources
If r.Type = pjResourceTypeMaterial Then
CustomFieldValueListAdd FieldID:=pjCustomTaskText1, Value:=r.Name
End If
Next r
End Sub
'This procedure calculates amount of material needed based on user entry into Number1
' and then assigns that material to each task with a selected material (Text1)
Sub MatlCost()
Dim t As Task
Dim PaintType As String
Dim Need As Variant
'assign paints to tasks with entry in Text1
For Each t In ActiveProject.Tasks
If t.Text1 <> "" Then
PaintType = t.Text1
Need = CDec(t.Number1 / ActiveProject.Resources(PaintType).Number1)
t.ResourceNames = PaintType & "[" & Need & "]"
End If
Next t

End Sub