Long Formula failing on multiline VBA

[ryanjames132001]

Hi, Im trying to implement long formula into VBA but its showing compile error

ange("D2:D" & Lastrow).Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(RC[-1],""*# of unattended process*""),MID(RC[5],(FIND(""("",RC[5])+1),FIND("")"",RC[5])-(FIND(""("",RC[5])+1)),IF(COUNTIF(RC[-1],""*Plug-ins using removed*""),MID(RC[5],(FIND(""("",RC[5])+1),FIND("")"",RC[5])-(FIND(""("",RC[5])+1)),IF(COUNTIF(RC[-1],""*# of slow smart services*""),MID(RC[5],1,(FIND("" ("",RC[5])-1)),IF(COUNTIF(RC[-1],""*# of slow expressions*""),MID(RC[5],(FIND(""."",RC[5])+1),FIND(""("",RC[5])-(FIND(""."",RC[5])+2)),IF(COUNTIF(RC[-1],""*# of slow integrations*""),MID(RC[5],(FIND(""."",RC[5])+1),FIND(""("",RC[5])-(FIND(""."",RC[5])+2)),IF(COUNTIF(RC[-1],""*# of slow Tempo reports*""),MID(RC[5],1,(FIND("" ("",RC[5])-1)),IF(COUNTIF(RC[-1],""*# of slow process tasks*""),MID(RC[5],(FIND(""("",RC[5])+1),FIND("")"",RC[5])-(FIND(""("",RC[5])+1)),IF(COUNTIF(RC[-1],""*# of slow stored procedure*""),CONCAT(""jdbc/Appian."",MID(RC[5],1,(FIND("" ("",RC[5])-1))),IF(COUNTIF(RC[-1],""*# of stored procedures with high*""),MID(G1,1,(FIND("" ("",G1)-1)),""ERROR"" "


what the code looks like formatted in notepad the below works when inserted into field but not on vba. I think its because its not all on 1 line however ive tried _ to break the code up without success


=IF(
COUNTIF(A1,"*# of unattended process*"),MID(G1,(FIND("(",G1)+1),FIND(")",G1)-(FIND("(",G1)+1)),
IF(
COUNTIF(A1,"*Plug-ins using removed*"),MID(G1,(FIND("(",G1)+1),FIND(")",G1)-(FIND("(",G1)+1)),
IF(
COUNTIF(A1,"*# of slow smart services*"),MID(G1,1,(FIND(" (",G1)-1)),
IF(
COUNTIF(A1,"*# of slow expressions*"),MID(G1,(FIND(".",G1)+1),FIND("(",G1)-(FIND(".",G1)+2)),
IF(
COUNTIF(A1,"*# of slow integrations*"),MID(G1,(FIND(".",G1)+1),FIND("(",G1)-(FIND(".",G1)+2)),
IF(
COUNTIF(A1,"*# of slow Tempo reports*"),MID(G1,1,(FIND(" (",G1)-1)),
IF(
COUNTIF(A1,"*# of slow process tasks*"),MID(G1,(FIND("(",G1)+1),FIND(")",G1)-(FIND("(",G1)+1)),
IF(
COUNTIF(A1,"*# of slow stored procedure*"),CONCAT("jdbc/Appian.",MID(G1,1,(FIND(" (",G1)-1))),
IF(
COUNTIF(A1,"*# of stored procedures with high*"),MID(G1,1,(FIND(" (",G1)-1)),
IF(
COUNTIF(A1,"*# of large SAIL*"),IF(COUNTIF(G1,"*Report:*"),MID(G1,1,FIND(")",G1)),CONCAT(LEFT(G1,100),"(more…)")),
IF(
COUNTIF(A1,"*Top Models by Process Execution Engine*"),MID(G1,1,(FIND(" (",G1)-1)),"Error"

)))))))))))

 

[ryanjames132001]

to add the beginning of code is missing R that's just because ive copied it into here incorrectly

 

[offthelip]

the best way to solve this sort of problem is to remove the equals sign at the start of the string, this changes it to just write text to the cell and will compile and run . Then go to the cell add the equals sign back in, EXCEL will then tell what the error is with what is written

 

[RoryA]

If that's the correct formula, the code would be:

Range("D2:D" & Lastrow).Formula = "=IF(COUNTIF(A1,""*# of unattended process*""),MID(G1,(FIND(""("",G1)+1),FIND("")"",G1)-(FIND(""("",G1)+1)),IF(COUNTIF(A1,""*Plug-ins using removed*""),MID(G1,(FIND(""("",G1)+1),FIND("")"",G1)-(FIND(""("",G1)+1)),IF(COUNTIF(A1,""*# of slow smart services*""),MID(G1,1,(FIND("" ("",G1)-1)),IF(COUNTIF(A1,""*# of slow expressions*""),MID(G1,FIND(""."",G1)+1),FIND(""("",G1)-(FIND(""."",G1)+2)),IF(COUNTIF(A1,""*# of slow integrations*""),MID(G1,(FIND(""."",G1)+1),FIND(""("",G1)-(FIND(""."",G1)+2)),IF(COUNTIF(A1,""*# of slow Tempo reports*""),MID(G1,1,(FIND("" ("",G1)-1)),IF(COUNTIF(A1,""*# of slow process tasks*""),MID(G1,(FIND(""("",G1)+1),FIND("")"",G1)-(FIND(""("",G1)+1)),IF(COUNTIF(A1,""*# of slow stored procedure*""),CONCAT(""jdbc/Appian."",MID(G1,1,(FIND("" ("",G1)-1))),IF(COUNTIF(A1,""*# of stored procedures with high*""),MID(G1,1,(FIND("" ("",G1)-1)),IF(COUNTIF(A1,""*# of large SAIL*""),IF(COUNTIF(G1,""*Report:*""),MID(G1,1,FIND("")"",G1)),CONCAT(LEFT(G1,100),""(more…)"")),IF(COUNTIF(A1,""*Top Models by Process Execution Engine*""),MID(G1,1,(FIND("" ("",G1)-1)),""Error"")))))))))))"

I suspect that formula could be shortened too.