After getting a lot of help from my company’s MS Project product manager, I customized the following VBA macro. He also recommended that I pick up a copy of Rod Gill’s "VBA Programming for Microsoft Office Project " which is now on my Christmas list.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Calc_Number2()
Dim intCounter As Integer Dim intArraySize As Integer
intArraySize = 2 ‘intArraySize is the number of elements in the arrays ‘ Arrays strTest and intMult must be = intArraySize Dim strTest(2) As Variant Dim intValue(2) As Integer Dim strTaskName As Variant
‘ This is where you define the test (strTest) condition and values(intValue) ‘ Note: if you have two test conditions that are true the last test condition ‘ will be the one that modifies Number2
‘ Tsk is an object that contains all task fields for a given task. The For – Next ‘ statement loops through every Project task and performs the test and calculations
For Each Tsk In ActiveProject.Tasks intCounter = 1 If Not Tsk Is Nothing Then ‘Do not process blank tasks. Keeps macro from blowing up. Do Until intCounter > intArraySize If Tsk.Name Like strTest(intCounter) Then Tsk.Number2 = (Tsk.Number1 * intValue(intCounter)) / 60 End If intCounter = intCounter + 1 Loop End If Next Tsk ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A big thank you to Joe Peltier for the above code!