VBA Code next line continuation

[Woftae]

Hi one and all,

I am trying to use VBA to put a very long formula into a cell, why such a long formula? Don't ask!
When the code is entered into the cell as a formula, manually, it worked fine.
But after coding the formula into VBA using multiple next line continuations " _" (because it is such a long formula) I am getting a "Compile Error: expected end of statement" error.
Is there any character limitation for VBA code for VBA/Formula etc or is there an error in the code that I am not seeing.
Muchos Gracious

Range("J2").Formula = "=IF(AND(E2=""Not Found"",ISERROR(FIND(""+"",D2))),""--"", _
IF AND(E2<>""Not Found"",ISERROR(FIND(""+"",VLOOKUP(A2,Products!B:C,2,FALSE)))),IF(Variables!A$7="""",""-x-"",VLOOKUP(D2,Latest!A:G,Variables!B$7,FALSE)), _
IF(E2=""Not Found"",MID(SUBSTITUTE(D2,"" "",""""),1,FIND(""+"",SUBSTITUTE(D2,"" "",""""))-1)&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""),1, _
FIND(""+"",SUBSTITUTE(D2,"" "",""""))-1),Latest!A:B,2,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""),1, _
FIND(""+"",SUBSTITUTE(D2,"" "",""""))-1),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)&"" + ""&MID(SUBSTITUTE(D2,"" "",""""), _
FIND(""+"",SUBSTITUTE(D2,"" "",""""))+1,LEN(SUBSTITUTE(D2,"" "","""")))&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""), _
FIND(""+"",SUBSTITUTE(D2,"" "",""""))+1,LEN(SUBSTITUTE(D2,"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""), _
FIND(""+"",SUBSTITUTE(D2,"" "",""""))+1,LEN(SUBSTITUTE(D2,"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE))), _
MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""),1,FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))-1)&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""),1, _
FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))-1),Latest!A:B,2,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""),1, _
FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))-1),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)&"" + ""&MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""), _
FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))+1,LEN(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "","""")))&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""), _
FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))+1,LEN(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""), _
FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))+1,LEN(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE))))))"

 

[offthelip]

The line continuation problem is because your code puts the under line symbol in the middle of a string so it looks just like a normal string character. The only way is to end one string on each line and then concatenate it with the string on the next line like this:

Range("J2").Formula = "=IF(AND(E2=""Not Found"",ISERROR(FIND(""+"",D2))),""--"", " _
& "IF AND(E2<>""Not Found"",ISERROR(FIND(""+"",VLOOKUP(A2,Products!B:C,2,FALSE)))),IF(Variables!A$7="""",""-x-"",VLOOKUP(D2,Latest!A:G,Variables!B$7,FALSE)), " _
& "IF(E2=""Not Found"",MID(SUBSTITUTE(D2,"" "",""""),1,FIND(""+"",SUBSTITUTE(D2,"" "",""""))-1)&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""),1," _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))-1),Latest!A:B,2,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""),1, " _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))-1),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)&"" + ""&MID(SUBSTITUTE(D2,"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))+1,LEN(SUBSTITUTE(D2,"" "","""")))&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))+1,LEN(SUBSTITUTE(D2,"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))+1,LEN(SUBSTITUTE(D2,"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE))), " _
& "MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""),1,FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))-1)&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""),1, " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))-1),Latest!A:B,2,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""),1, " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))-1),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)&"" + ""&MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))+1,LEN(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "","""")))&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))+1,LEN(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))+1,LEN(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE))))))"

Note this solves the compilation probelm but on my computer is comes back as an invalid equation. To help solve this I suggest you you change :

Range("J2").Formula = "=IF .....etc

to

Range("J2").value = "IF .... etc(

#then edit the cell and put the equal back in then excel will tell you where the error is.

 

[Woftae]

The line continuation problem is because your code puts the under line symbol in the middle of a string so it looks just like a normal string character. The only way is to end one string on each line and then concatenate it with the string on the next line like this:

Range("J2").Formula = "=IF(AND(E2=""Not Found"",ISERROR(FIND(""+"",D2))),""--"", " _
& "IF AND(E2<>""Not Found"",ISERROR(FIND(""+"",VLOOKUP(A2,Products!B:C,2,FALSE)))),IF(Variables!A$7="""",""-x-"",VLOOKUP(D2,Latest!A:G,Variables!B$7,FALSE)), " _
& "IF(E2=""Not Found"",MID(SUBSTITUTE(D2,"" "",""""),1,FIND(""+"",SUBSTITUTE(D2,"" "",""""))-1)&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""),1," _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))-1),Latest!A:B,2,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""),1, " _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))-1),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)&"" + ""&MID(SUBSTITUTE(D2,"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))+1,LEN(SUBSTITUTE(D2,"" "","""")))&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))+1,LEN(SUBSTITUTE(D2,"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(D2,"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(D2,"" "",""""))+1,LEN(SUBSTITUTE(D2,"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE))), " _
& "MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""),1,FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))-1)&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""),1, " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))-1),Latest!A:B,2,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""),1, " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))-1),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)&"" + ""&MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))+1,LEN(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "","""")))&"" = ""&IF(ISERROR(VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))+1,LEN(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE)),""Not Found"",VLOOKUP(MID(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""), " _
& "FIND(""+"",SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))+1,LEN(SUBSTITUTE(VLOOKUP(A2,Products!B:C,2,FALSE),"" "",""""))),INDIRECT(CONCATENATE(""Latest!"",(Variables!A$3),"":"",(Variables!A$4))),Variables!B$4,FALSE))))))"

Note this solves the compilation probelm but on my computer is comes back as an invalid equation. To help solve this I suggest you you change :

Range("J2").Formula = "=IF .....etc

to

Range("J2").value = "IF .... etc(

#then edit the cell and put the equal back in then excel will tell you where the error is.

offthelip
Thank you so much for taking the time to reply AND for providing the solution AND for fixing the code itself, it is much appreciated.
As for the invalid equation error, the cause was the missing parenthesis "(" after the IF statement on the 2nd line:

& "IF AND(E2<>""Not Found"",........

Thank you again