Rookie trying to fix formula

  • Hi all,


    I think I got myself in a bit over my head here. I have a very long formula and am struggling to break it out. I found a few helpful threads on here but I cannot mimic the solution accurately.


    I am trying to split the lines but have been unsuccessful in having the macro work. Each number represents a date. The formulas help to ensure that the best date is always provided.




    I have also added the formulas. Each of the formulas has been tested separately and as a whole. Here are the formulas separated below.


      Formulas Needed Based on Value
    Value Formula Needed Formula Needed
    1 IF(AD2=1,R2  
    2 IF(And(AD2=2, AC2>1),T2 IF(AND(AD2=2,AC2<1),J2
    3 IF(AND(AD2=3, AC2>1),T2 IF(AND(AD2=3,AC2<1),J2
    4 IF(AND(AD2=4,AC2>1),T2 IF(AND(AD2=4,AC2<1),J2
    5 IF(AD2=5,S2)  
    6 IF(AND(AD2=6,AC2>1,T2 IF(AND(AD2=6,AC2<1,J2
    7 IF(AND(AD2=7,AC2>1,T2 IF(AND(AD2=7,AC2<1,J2
    10 IF(AD2=10,R2)  
    15 IF(AD2=15,S2)  
    16 IF(AND(AD2=16,AC2>1),T2 IF(AND(AD2=16,AC2<1),J2
    17 IF(AND(AD2=17,AC2>1),T2 IF(AND(AD2=17,AC2<1),J2
    18 IF(AND(AD2=18,AC2>1),T2 IF(AND(AD2=18,AC2<1),J2
    19 IF(AND(AD2=19,AC2>1),T2 IF(AND(AD2=19,AC2<1),J2
    20 IF(AD2=20,S2)  
    21 IF(AND(AD2=21,AC2>1),T2 IF(AND(AD2=21,AC2<1),J2
    22 IF(AND(AD2=22,AC2>1),T2 IF(AND(AD2=22,AC2<1),J2
    24 IF(AND(AD2=24,AC2>1),T2 IF(AND(AD2=24,AC2<1),J2
    25 IF(AD2=25,Today())  
    26 IF(AD2=26,""Vehicle Hidden"")  
    27 IF(AD2=27,""See SCS Specialist"")  
    28 IF(AND(AD2=28,T2>1),T2 IF(AND(AD2=28,T2<1),J2
    29 IF(AND(AD2=29,AC2>1),T2 IF(AND(AD2=29,AC2<1),J2
    30 IF(AND(AD2=30,AC2>1),T2 IF(AND(AD2=30,AC2<1),J2
    31 IF(AND(AD2=31,AC2>1),T2 IF(AND(AD2=31,AC2<1),J2
    32 IF(AND(AD2=32,AC2>1),T2 IF(AND(AD2=32,AC2<1),J2
    33 IF(AD2=33,Today()  
    35 IF(AD2=35,Today()  
    36 IF(AD2=36,""See SCS Specialist"")  
    37 IF(AD2=37,""See SCS Specialist"")  
    38 IF(AD2=38,""See SCS Specialist"")  
    39 IF(AD2=39,""See SCS Specialist"")  
    40 IF(AD2=40,""See SCS Specialist"")  
    45 IF(AD2=45,""See SCS Specialist"")  
    46 IF(AD2=46,""See SCS Specialist"")  
    50 IF(AD2=50,""See SCS Specialist"")  
    60 IF(AD2=60,""See SCS Specialist"")  
    70 IF(AD2=70,""See SCS Specialist"")  
    75 IF(AD2=75,""See SCS Specialist"")  
    76 IF(AD2=76,J2)  
    77 IF(AD2=77,J2)  
    78 IF(AD2=78,J2)  
    79 IF(AD2=79,J2)  
    80 IF(AD2=80,J2)  
      IF(AD2="" "",J2)  


    If Anybody cares to look at this, I would appreciate it very much. I have worked too long for this to fail but I am at a loss as to why it will not work.

    Edited once, last by royUK: add code tags ().

  • It seems to me like you'll need to

    1)define some variables,

    2) setup a loop

    3) insert your if then criteria in this loop as it progresses from the top of the spreadsheet to the bottom within the above mentioned loop.


    Does this make sense?

  • alternatively , you could also try using nested if then formulas. Excel allows upto six if then criteria, if i remember correctly.


    would look something like this.


    =IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))

  • Hi Ayjay,


    I had originally made a nested formula but I wasn't able to get it to work successfully. I think I used more than six. I will try making a smaller formula in each line to see if this will work.


    Yesterday, I worked to learn how to make a macro loop. I am trying to decide if I want to use the nested formula or a macro loop. I believe the macro loop is the best way to do this.


    Thank you,


    Grenaldo

  • I revised my formula. It now works. The original lines were too long. I also needed to add commas to ensure that the formula continued with each line as well. In addition, the parentheses ending each formula had to be placed at the end of the sub.

    ActiveCell.FormulaR1C1 = "IF(AD2=1,R2," & _

    "IF(AND(AD2=2,AC2>1),T2,IF(AND(AD2=2,AC2<1),J2,IF(AND(AD2=3,AC2>1),T2,IF(AND(AD2=3,AC2<1),J2," & _

    "IF(AND(AD2=4,AC2>1),T2,IF(AND(AD2=4,AC2<1),J2,IF(AD2=5,S2,IF(AD2=6,T2,IF(AD2=7,T2,IF(AD2=10,R2," & _

    "IF(AD2=15,S2,IF(AND(AD2=16,AC2>1),T2,IF(AND(AD2=16,AC2<1),J2,IF(AND(AD2=17,AC2>1),T2,IF(AND(AD2=17,AC2<1),J2," & _

    "IF(AND(AD2=18,AC2>1),T2,IF(AND(AD2=18,AC2<1),J2,IF(AND(AD2=19,AC2>1),T2,IF(AND(AD2=19,AC2<1),J2,IF(AD2=20,S2,IF(AND(AD2=21,AC2>1),T2," & _

    "IF(AND(AD2=21,AC2<1),J2,IF(AD2=25,TODAY(),IF(AD2=26,TODAY()+150,IF(AD2=27,TODAY()+150,IF(AND(AD2=28,T2>1),T2," & _

    "IF(AND(AD2=28,T2<1),J2,IF(AND(AD2=29,AC2>1),T2,IF(AND(AD2=29,AC2<1),J2,IF(AND(AD2=30,AC2>1),T2,IF(AND(AD2=30,AC2<1),J2," & _

    "IF(AND(AD2=31,AC2>1),T2,IF(AND(AD2=31,AC2<1),J2,IF(AND(AD2=32,AC2>1),T2,IF(AND(AD2=32,AC2<1),J2,IF(AD2=33,TODAY()," & _

    "IF(AD2=35,TODAY(),IF(AD2=36,TODAY()+150,IF(AD2=37,TODAY()+150,IF(AD2=38,TODAY()+150,IF(AD2=39,TODAY()+150," & _

    "IF(AD2=40,TODAY()+150,IF(AD2=45,TODAY()+150,IF(AD2=46,TODAY()+150,IF(AD2=50,TODAY()+150,IF(AD2=60,TODAY()+150," & _

    "IF(AD2=70,TODAY()+150,IF(AD2=75,TODAY()+150,IF(AD2=76,J2,IF(AD2=77,J2,IF(AD2=78,J2,IF(AD2=79,J2," & _

    "IF(AD2=80,J2,IF(AD2="" "",J2,IF(AND(AD2=22,AC2>1),T2,IF(AND(AD2=22,AC2<1),J2," & _

    "IF(AND(AD2=24,AC2>1),T2,IF(AND(AD2=24,AC2<1),J2,)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))"


    I still believe the loop was better to do but It was easier to edit the formula than learning how to create loops with advanced logic at this time.


    Thank you,


    Grenaldo