VBA Compile Error: Syntax Error, From Very Long Formula

    Hi,


    I just entered a very long formula in a worksheet and it works fine:


    Code
    =IF(IF(IF(B4="N/A",3,6)=SUM(IF(C4="N/A","",IF(VLOOKUP(C4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)="",0,1))+IF(D4="N/A","",IF(VLOOKUP(D4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)="",0,1))+IF(E4="N/A","",IF(VLOOKUP(E4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)="",0,1))+IF(F4="N/A",0,IF(VLOOKUP(F4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)="",0,1))+IF(G4="N/A",0,IF(VLOOKUP(G4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)="",0,1))+IF(LEFT(H4,14)="Single Element",1,IF(H4="N/A",0,IF(VLOOKUP(H4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)="",0,1)))),"In","Out")="in",IF(B4="N/A",SUM(VLOOKUP($C4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)+VLOOKUP($D4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)+VLOOKUP($E4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)),SUM(VLOOKUP($C4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)+VLOOKUP($D4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)+VLOOKUP($E4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)+VLOOKUP($F4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)+VLOOKUP($G4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE)+VLOOKUP($H4,'Supplier Compoents Entry'!$A$1:$G$1000,6,FALSE))),"")


    I tried recording it, but I get and Error (Compile Error: Syntax Error) when trying to play it. this is what the VBA that was recorded looks like:


    Code
    Range("I4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(IF(IF(RC[-7]=""N/A"",3,6)=SUM(IF(RC[-6]=""N/A"","""",IF(VLOOKUP(RC[-6],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(RC[-5]=""N/A"","""",IF(VLOOKUP(RC[-5],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(RC[-4]=""N/A"","""",IF(VLOOKUP(RC[-4],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(RC[-3]=""N/A"",0,IF(VLOOKUP(RC[-3],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(RC[-2]=""N/A"",0,IF(VLOOKUP(RC[-2],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(LEFT(RC[-1],14)=""Single Element"",1,IF(RC[-1]=""N/A"",0,IF(VLOOKUP(RC[-1],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1)))),""In"",""Out"")=""in"",IF(RC[-7]=""N/A"",SUM(VLOOKUP(RC3,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC4,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC5,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)),SUM(VLOOKUP(RC3,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC4,'Supplier Compoents Entry'!R1C1:R10
00C7,6,FALSE)+VLOOKUP(RC5,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC6,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC7,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC8,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE))),"""")"


    The code looks okay, but it is so long the VBA is forcing a hard return half way through and I think that this is what is causing the error, is there a way around this or do I have to split the formula into to cells?


    Thanks,


    Anthony

    Re: VBA Compile Error: Syntax Error, From Very Long Formula


    My Supplier enter the components on worksheet (Supplier Compoents Entry), I then assemble all their components in assemblies of 3, 5 or 6 components) and if all components are available then that assembly becomes available. The Easy way out if for me to split up the "are all components available" and the "go get all the component prices" into to separate formulas.



    Code
    Range("I4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(IF(RC[-7]=""N/A"",3,6)=SUM(IF(RC[-6]=""N/A"","""",IF(VLOOKUP(RC[-6],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(RC[-5]=""N/A"","""",IF(VLOOKUP(RC[-5],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(RC[-4]=""N/A"","""",IF(VLOOKUP(RC[-4],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(RC[-3]=""N/A"",0,IF(VLOOKUP(RC[-3],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(RC[-2]=""N/A"",0,IF(VLOOKUP(RC[-2],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1))+IF(LEFT(RC[-1],14)=""Single Element"",1,IF(RC[-1]=""N/A"",0,IF(VLOOKUP(RC[-1],'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)="""",0,1)))),""In"",""Out"")"
 
   Range("J4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]=""in"",IF(RC[-8]=""N/A"",SUM(VLOOKUP(RC3,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC4,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC5,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)),SUM(VLOOKUP(RC3,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC4,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC5,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC6,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC7,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE)+VLOOKUP(RC8,'Supplier Compoents Entry'!R1C1:R1000C7,6,FALSE))),"""")"



    So if "I4" returns "In", "J4" goes and looks up the prices.

    Re: VBA Compile Error: Syntax Error, From Very Long Formula


    I suspect it's just the way that the formula is laid out in the VBA statement. It needs a few more line breaks, as there is too much code for a single VBA line. This runs for me, and assigns the formula to the cell, although I have no idea what it will or should return:


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: VBA Compile Error: Syntax Error, From Very Long Formula


    Hi Batman,


    Sorry for taking so long to get back to you, That worked perfectly, I didn't know I could use the "& _" to tell is to add the following row.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login