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.

Code

- ActiveCell.FormulaR1C1 = "=RIGHT(RC[1],2)"
- Range("AE2").Select
- Selection.AutoFill Destination:=Range("AE2:AE1844")
- Range("AE2:AE1844").Select
- Range("AD1").Select
- ActiveCell.FormulaR1C1 = "P-Status Complete"
- Range("AD2").Select
- ActiveCell.FormulaR1C1 = "=VALUE(RC[1])"
- Range("AD2").Select
- Selection.AutoFill Destination:=Range("AD2:AD5832")
- Range("AD2:AD5832").Select
- Columns("AJ:AJ").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Selection.ColumnWidth = 23.71
- Range("AJ1").Select
- ActiveCell.FormulaR1C1 = "Customer Reason Code"
- Range("AJ1").Select
- ActiveCell.FormulaR1C1 = "Final Estimated Delivery Date"
- End Sub
- Sub POSODateFormula()
- Range("U2").Select
- 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,""Vehicle Hidden"",IF(AD2=27,""See SCS Specialist"",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,""See SCS Specialist"",IF(AD2=37,""See SCS Specialist""))))))))))))" & _
- "IF(AD2=38,""See SCS Specialist"",IF(AD2=39,""See SCS Specialist"",IF(AD2=40,""See SCS Specialist"",IF(AD2=45,""See SCS Specialist"",IF(AD2=46,""See SCS Specialist"",IF(AD2=50,""See SCS Specialist"",IF(AD2=60,""See SCS Specialist"",IF(AD2=70,""See SCS Specialist"",IF(AD2=75,""See SCS Specialist"")))))))))" & _
- "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))))))))))"
- Range("U2").Select
- Selection.AutoFill Destination:=Range("U2:U5832")
- Range("U2:U5832").Select
- Range("AJ2").Select
- ActiveCell.FormulaR1C1 = "=IF(RC[-6]=9000,""Yes"",""No"")"
- Range("AJ2").Select
- Selection.AutoFill Destination:=Range("AJ2:AJ5832")
- Range("AJ2:AJ5832").Select
- End Sub

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.