Reply To: Number of nested IIF statements possible?

[Community]

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

strTest(1) = "*ab*"
intValue(1) = 50
strTest(2) = "*bc*"
intValue(2) = 30

Dim Tsk As Task

‘ 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!