VBA Code Error

[ai1094]

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.

 

[steve the fish]

The macro recorder up until recent versions of excel just didnt work with long formulas. It cuts off 8 digits at each line feed from memory. See if this is better:

    Range("AB82").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[" & _
        "-11]),""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"")))))"

 

[ai1094]

Looks like this worked. Will keep you updated if this is exactly what is needed, but thank you!!

The macro recorder up until recent versions of excel just didnt work with long formulas. It cuts off 8 digits at each line feed from memory. See if this is better:

    Range("AB82").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[" & _
        "-11]),""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"")))))"