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
Display More
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.