Hi, I have the following code that I am trying to insert in a specific cell via macro record:
=IF(AND(AA82="Both",IFERROR(YEAR(Q82),"A")=IFERROR(YEAR(D82),"B"),IFERROR(VLOOKUP(S82,Summary!A:B,2,FALSE),0)<>0),"DEL",IF(AND(AA82="Both",IFERROR(YEAR(Q82),"A")=IFERROR(YEAR(D82),"B"),IFERROR(VLOOKUP(S82,Summary!A:B,2,FALSE),1)=""),"TURN OFF LEASE - LEAVE ASSETS",IF(AND(AA82="Both",IFERROR(YEAR(Q82),"A")<>IFERROR(YEAR(D82),"B"),IFERROR(VLOOKUP(S82,Summary!A:B,2,FALSE),0)<>0),"TURN OFF ASSETS - LEAVE LEASE",IF(AND(AA82="CAP ONLY",IFERROR(VLOOKUP(S82,Summary!A:B,2,FALSE),0)<>0),"TURN OFF ASSETS",IF(AND(AA82="LEASE ONLY",IFERROR(YEAR(Q82),"A")=IFERROR(YEAR(D82),"B")),"TURN OFF LEASE","DO NOTHING")))))
It's a long formula, I know. However, when I select the cell and copy the formula, VBA records as the following:
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]=""Both"",IFERROR(YEAR(RC[-11]),""A"")=IFERROR(YEAR(RC[-24]),""B""),IFERROR(VLOOKUP(RC[-9],Summary!C[-27]:C[-26],2,FALSE),0)<>0),""DEL"",IF(AND(RC[-1]=""Both"",IFERROR(YEAR(RC[-11]),""A"")=IFERROR(YEAR(RC[-24]),""B""),IFERROR(VLOOKUP(RC[-9],Summary!C[-27]:C[-26],2,FALSE),1)=""""),""TURN OFF LEASE - LEAVE ASSETS"",IF(AND(RC[-1]=""Both"",IFERROR(YEAR(RC["& _
"A"")<>IFERROR(YEAR(RC[-24]),""B""),IFERROR(VLOOKUP(RC[-9],Summary!C[-27]:C[-26],2,FALSE),0)<>0),""TURN OFF ASSETS - LEAVE LEASE"",IF(AND(RC[-1]=""CAP ONLY"",IFERROR(VLOOKUP(RC[-9],Summary!C[-27]:C[-26],2,FALSE),0)<>0),""TURN OFF ASSETS"",IF(AND(RC[-1]=""LEASE ONLY"",IFERROR(YEAR(RC[-11]),""A"")=IFERROR(YEAR(RC[-24]),""B"")),""TURN OFF LEASE"",""DO NOTHING"")))))""& _
"?????
It's inserting the question marks at the end I can't figure out why. Any suggestions? Thanks.
=IF(AND(AA82="Both",IFERROR(YEAR(Q82),"A")=IFERROR(YEAR(D82),"B"),IFERROR(VLOOKUP(S82,Summary!A:B,2,FALSE),0)<>0),"DEL",IF(AND(AA82="Both",IFERROR(YEAR(Q82),"A")=IFERROR(YEAR(D82),"B"),IFERROR(VLOOKUP(S82,Summary!A:B,2,FALSE),1)=""),"TURN OFF LEASE - LEAVE ASSETS",IF(AND(AA82="Both",IFERROR(YEAR(Q82),"A")<>IFERROR(YEAR(D82),"B"),IFERROR(VLOOKUP(S82,Summary!A:B,2,FALSE),0)<>0),"TURN OFF ASSETS - LEAVE LEASE",IF(AND(AA82="CAP ONLY",IFERROR(VLOOKUP(S82,Summary!A:B,2,FALSE),0)<>0),"TURN OFF ASSETS",IF(AND(AA82="LEASE ONLY",IFERROR(YEAR(Q82),"A")=IFERROR(YEAR(D82),"B")),"TURN OFF LEASE","DO NOTHING")))))
It's a long formula, I know. However, when I select the cell and copy the formula, VBA records as the following:
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]=""Both"",IFERROR(YEAR(RC[-11]),""A"")=IFERROR(YEAR(RC[-24]),""B""),IFERROR(VLOOKUP(RC[-9],Summary!C[-27]:C[-26],2,FALSE),0)<>0),""DEL"",IF(AND(RC[-1]=""Both"",IFERROR(YEAR(RC[-11]),""A"")=IFERROR(YEAR(RC[-24]),""B""),IFERROR(VLOOKUP(RC[-9],Summary!C[-27]:C[-26],2,FALSE),1)=""""),""TURN OFF LEASE - LEAVE ASSETS"",IF(AND(RC[-1]=""Both"",IFERROR(YEAR(RC["& _
"A"")<>IFERROR(YEAR(RC[-24]),""B""),IFERROR(VLOOKUP(RC[-9],Summary!C[-27]:C[-26],2,FALSE),0)<>0),""TURN OFF ASSETS - LEAVE LEASE"",IF(AND(RC[-1]=""CAP ONLY"",IFERROR(VLOOKUP(RC[-9],Summary!C[-27]:C[-26],2,FALSE),0)<>0),""TURN OFF ASSETS"",IF(AND(RC[-1]=""LEASE ONLY"",IFERROR(YEAR(RC[-11]),""A"")=IFERROR(YEAR(RC[-24]),""B"")),""TURN OFF LEASE"",""DO NOTHING"")))))""& _
"?????
It's inserting the question marks at the end I can't figure out why. Any suggestions? Thanks.