Hello,
I am new to posting in this forum and fairly new to Macros/VBA so please excuse me if I miss anything.
I created the following long formula that gets me the info I need to create an accounts receivable aging report:
=IF(X2="CM", "Credit", IF( X2="PMT", "Unapplied", IF(X2="INV", IF(AND(AB2<=TODAY(),AB2>=TODAY()-7),"0-7",IF(AND(AB2<=TODAY()-8,AB2>=TODAY()-30),"8-30", IF(AB2<=TODAY()-31,">30",IF(AND(AB2>TODAY(),AB2<=(TODAY()+8)),"Within 7",IF(AND(AB2>=(TODAY()+8),AB2<= (EOMONTH(TODAY(),0))),TEXT(TODAY(),"MMMM"), IF(AND(AB2>=EOMONTH(TODAY()+1,0),AB2<=EOMONTH(TODAY(),1)),TEXT(EOMONTH(TODAY(),1),"mmmm"),IF(AB2>EOMONTH(TODAY(),1),TEXT(EOMONTH(TODAY(),1)+1,"MMMM +"), FALSE))))))))))
<tbody>
</tbody>
I then recorded a Macro using the formula, which recorded the following:
When I run the macro it gives me the error
"Run-time error '1004':
Application-defined or object-defined or object defined error"
I was reading that Excel only allows 7 nested ifs, is that the issue here?
Any help is greatly appreciated as I am trying to learn.
If it helps, I was also trying to copy what was done in this post with no luck.
I am new to posting in this forum and fairly new to Macros/VBA so please excuse me if I miss anything.
I created the following long formula that gets me the info I need to create an accounts receivable aging report:
=IF(X2="CM", "Credit", IF( X2="PMT", "Unapplied", IF(X2="INV", IF(AND(AB2<=TODAY(),AB2>=TODAY()-7),"0-7",IF(AND(AB2<=TODAY()-8,AB2>=TODAY()-30),"8-30", IF(AB2<=TODAY()-31,">30",IF(AND(AB2>TODAY(),AB2<=(TODAY()+8)),"Within 7",IF(AND(AB2>=(TODAY()+8),AB2<= (EOMONTH(TODAY(),0))),TEXT(TODAY(),"MMMM"), IF(AND(AB2>=EOMONTH(TODAY()+1,0),AB2<=EOMONTH(TODAY(),1)),TEXT(EOMONTH(TODAY(),1),"mmmm"),IF(AB2>EOMONTH(TODAY(),1),TEXT(EOMONTH(TODAY(),1)+1,"MMMM +"), FALSE))))))))))
H | X | AA | AB | AC |
Due Date | Class | Due Count | Due Date Formatted | Column |
2015.02.05 | INV | -30 | 2/5/2015 | February |
<tbody>
</tbody>
I then recorded a Macro using the formula, which recorded the following:
Code:
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]=""CM"",""Credit"",IF(RC[-5]=""PMT"",""Unapplied"",IF(RC[-5]=""INV"",IF(AND(RC[-1]<=TODAY(),RC[-1]>=TODAY()-7),""0-7"",IF(AND(RC[-1]<=TODAY()-8,RC[-1]>=TODAY()-30),""8-30"",IF(RC[-1]<=TODAY()-31,"">30"",IF(AND(RC[-1]>TODAY(),RC[-1]<=(TODAY()+8)),""Within 7"",IF(AND(RC[-1]>=(TODAY()+8),RC[-1]<=(EOMONTH(TODAY(),0))),TEXT(TODAY(),""MMMM""),IF(AND(RC[-1]>=EOMO" & _
"Y()+1,0),RC[-1]<=EOMONTH(TODAY(),1)),TEXT(EOMONTH(TODAY(),1),""MMMM""),IF(RC[-1]>EOMONTH(TODAY(),1),TEXT(EOMONTH(TODAY(),1)+1,""MMMM +""),FALSE))))))))))"
When I run the macro it gives me the error
"Run-time error '1004':
Application-defined or object-defined or object defined error"
I was reading that Excel only allows 7 nested ifs, is that the issue here?
Any help is greatly appreciated as I am trying to learn.
If it helps, I was also trying to copy what was done in this post with no luck.