Loading...
Quick Links

Number of nested IIF statements possible?

Home Forums Discussion Number of nested IIF statements possible?

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #4849 Reply
    CommunityCommunity
    Keymaster

    I created a big nested IIF statement (41 levels) in MSProj 2007 Standard edition and I get an error statement. When I cut the statement down a lot, to like 3 or 4 levels (which is about where it begins highlighting the supposed error), it works fine. What I’m trying to do is calculate a work estimate based upon information contained in the task name (specific 4 character budget numbers). Each of 40 different budget numbers belong to one of 5 different estimating values. I’m trying to write a VBA macro, but it is much harder to write to have the same functionality as the 41 level nested IIF formula. Note: I’m using the Number1 and Number2 fields. The formula is for the Number2 field. The Number1 field just has numeric values ranging from 1 to 250, based upon the task. Nested IIF formala for reference: IIf([Name] Like “*6833*”,[Number1]*90/60, IIf([Name] Like “*6834*”,[Number1]*60/60, IIf([Name] Like “*6501*”,[Number1]*50/60, IIf([Name] Like “*6817*”,[Number1]*50/60, IIf([Name] Like “*6818*”,[Number1]*50/60, IIf([Name] Like “*6511*”,[Number1]*50/60, IIf([Name] Like “*6810*”,[Number1]*50/60, IIf([Name] Like “*6502*”,[Number1]*40/60, IIf([Name] Like “*6835*”,[Number1]*40/60, IIf([Name] Like “*6840*”,[Number1]*40/60, IIf([Name] Like “*6841*”,[Number1]*40/60, IIf([Name] Like “*6842*”,[Number1]*40/60, IIf([Name] Like “*6843*”,[Number1]*40/60, IIf([Name] Like “*6870*”,[Number1]*40/60, IIf([Name] Like “*6203*”,[Number1]*30/60, IIf([Name] Like “*6205*”,[Number1]*30/60, IIf([Name] Like “*6206*”,[Number1]*30/60, IIf([Name] Like “*6210*”,[Number1]*30/60, IIf([Name] Like “*6520*”,[Number1]*30/60, IIf([Name] Like “*6530*”,[Number1]*30/60, IIf([Name] Like “*6815*”,[Number1]*30/60, IIf([Name] Like “*6826*”,[Number1]*30/60, IIf([Name] Like “*6831*”,[Number1]*30/60, IIf([Name] Like “*6836*”,[Number1]*30/60, IIf([Name] Like “*6837*”,[Number1]*30/60, IIf([Name] Like “*6875*”,[Number1]*30/60, IIf([Name] Like “*6932*”,[Number1]*30/60, IIf([Name] Like “*6937*”,[Number1]*30/60, IIf([Name] Like “*6820*”,[Number1]*20/60, IIf([Name] Like “*6830*”,[Number1]*20/60, IIf([Name] Like “*6845*”,[Number1]*20/60, IIf([Name] Like “*6850*”,[Number1]*20/60, IIf([Name] Like “*6401*”,[Number1]*10/60, IIf([Name] Like “*6402*”,[Number1]*10/60, IIf([Name] Like “*6403*”,[Number1]*10/60, IIf([Name] Like “*6410*”,[Number1]*10/60, IIf([Name] Like “*6420*”,[Number1]*10/60, IIf([Name] Like “*6604*”,[Number1]*10/60, IIf([Name] Like “*6621*”,[Number1]*10/60, IIf([Name] Like “*6706*”,[Number1]*10/60, IIf([Name] Like “*6860*”,[Number1]*10/60, [Number1]*30/60)))))))))))))))))))))))))))))))))))))))))

     

     

    Thanks and I really appreciate any help you can provide! Larry Schiff

    #6046 Reply
    CommunityCommunity
    Keymaster
    I think formulae are limited to 256 characters! Your solution is to either convert the formula to VBA code (but you would have to run it to refresh the custom field). Alternatively you are going to have to simplify the formula and break it into 4 or 5 different custom text fields that either return NA or teh answer. A further field will then return the result of any of the earlier fields not displaying NA. Personally I prefer VBA as it would be much easier to maintain and update.
    Rod Gill
    #6054 Reply
    CommunityCommunity
    Keymaster
    Wow!  A very IFFY propsition, Larry!  I agree, this is going to be way too long as I’m pretty sure the 256 character limit applies.  A VBA macro is probably the best option because of this limitation.  If the budget numbers were in ranges that might help to cut down the length, but I’m guessing that’s not the case.
    Matthew Davis
    #6086 Reply
    CommunityCommunity
    Keymaster
    LOL and thanks Matthew!   VBA is the direction I went and it works.

    Larry Schiff
    #6085 Reply
    CommunityCommunity
    Keymaster

    Thanks for the helpful suggestions Rod, I appreciate it!   I went with a VBA macro and it is working.

     

    Larry Schiff

    #6087 Reply
    CommunityCommunity
    Keymaster
    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!
    #6129 Reply
    CommunityCommunity
    Keymaster
    WOW never know you could do so many nested IIF statements. Thanks you for sharing
    Teresa Alsdurf
Viewing 7 posts - 1 through 7 (of 7 total)
Reply To: Number of nested IIF statements possible?
Your information: