Formula too long

[cornepiek]

Hi ALl

Can someone please help me, i am trying to use a formula in a VBA script but it seems the formula is too long

The formula works perfectly in the cell, but when i try to insert it on the VBA script, i get a Runtime 1004 error.

This is my insanely cumbersome formula


=IFERROR(VLOOKUP((LEFT(A2,12))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,11))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,10))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,9))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,8))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,7))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,6))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,5))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,4))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,3))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,2))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,1))+0,PIP!$A:$D,4,FALSE),""))))))))))))

Can anyone please help me so simplify it without losing the functionality

 

[VoG]

Try

=IFERROR(VLOOKUP((LEFT(A2,12))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,11))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP(( LEFT(A2,10))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,9))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,8))+0,PIP!$ A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,7))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,6))+0,PIP!$A:$D,4,FALSE),IFERRO R(VLOOKUP((LEFT(A2,5))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,4))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,3 ))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,2))+0,PIP!$A:$D,4,FALSE),IFERROR(VLOOKUP((LEFT(A2,1))+0,PIP!$A:$D,4,FAL SE),""""))))))))))))

 

[Andrew Poulsom]

Please post your VBA code.

 

[steve the fish]

Recorded macros with long formulas seem to fail because excel cuts around 10 (I forget) characters from the VBA before the underscore/next line. Well it does on my 2010 version.

 

[cornepiek]

here is what the macro recorder captures


ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP((LEFT(RC[-2],12))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],11))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],10))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],9))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],8))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],7))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],6))+0,PIP!C" & _
"ALSE),IFERROR(VLOOKUP((LEFT(RC[-2],5))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],4))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],3))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],2))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],1))+0,PIP!C1:C4,4,FALSE),""""))))))))))))"
Range("C3").Select


i see that it misses some info on the breaks

lm going to try to write it manualy

 

[cornepiek]

This is the manual entry

Range("C2").Select


ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP((LEFT(RC[-2],12))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],11))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],10))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],9))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],8))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],7))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],6))+0,PIP!" & _
"C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],5))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],4))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],3))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],2))+0,PIP!C1:C4,4,FALSE),IFERROR(VLOOKUP((LEFT(RC[-2],1))+0,PIP!C1:C4,4,FALSE),""""))))))))))))"

 

[Andrew Poulsom]

Your code works for me.