My formula in VBA is too long? Is this really a thing? How to fix?

Brass Contributor

I have a section in my code which uses a formula. It works fine in excel but when I put it in VBA, it will not put the formula in the cell (i.e. cell B2). The coding should be correct as I simply recorded a macro and copied that code into the VBA code (which I've done before without issue with a smaller version of the formula). 

 

When I step through that section of the code, I get this error:

 

"unable to set the formulaarray property of the range class"

 

A google search suggests that there is a 255 character limit to formulas...which I certainly exceed. Is this really a thing? Is there a way around it?

 

Code follows below.

 

Though it seems crazy, I have, like, 12 of these formulas in the code.

 

Thanks,

 

Greg

 

using: Microsoft Office 365 ProPlus

------------------------------

 

 


Selection.FormulaArray = _
"=IFERROR(IF(ISNA(VLOOKUP(RC1,UndilutedPLUS!C1:C18,14,FALSE)),(IF(INDEX(DilutedPLUS!C[12],MATCH(RC[-1]&1,DilutedPLUS!C[-1]&DilutedPLUS!C[12],0))=1,INDEX(DilutedPLUS!C,MATCH(RC[-1]&1,DilutedPLUS!C[-1]&DilutedPLUS!C[12],0)),INDEX(DilutedPLUS!C,MATCH(RC[-1]&0,DilutedPLUS!C[-1]&DilutedPLUS!C[12],0)))),(IF(VLOOKUP(RC1,UndilutedPLUS!C1:C18,14,FALSE)=1,VLOOKUP(RC1,Undiluted" & _
"PLUS!C1:C18,2,FALSE),IF(VLOOKUP(RC1,DilutedPLUS!C1:C18,14,FALSE)=1,VLOOKUP(RC1,DilutedPLUS!C1:C18,2,FALSE),(IF(INDEX(DilutedPLUS!C[12],MATCH(RC[-1]&1,DilutedPLUS!C[-1]&DilutedPLUS!C[12],0))=1,INDEX(DilutedPLUS!C,MATCH(RC[-1]&1,DilutedPLUS!C[-1]&DilutedPLUS!C[12],0)),INDEX(DilutedPLUS!C,MATCH('Final Data'!RC[-1]&0,DilutedPLUS!C[-1]&DilutedPLUS!C[12],0)))))))),VLOOKUP" & _
"(RC1,UndilutedPLUS!C1:C18,2,FALSE))" & _
""

1 Reply

So, if there's an easy way, let me know. I don't want to get into breaking the formula down into 4 formulas and stitching it back together. Twelve times. And then someday I'll need to modify it and have to do it all again! 

 

In the meanwhile, I think I can just have a dreaded "Formulas" tab, and have VBA copy/past it where I need to and autofill the rows to the end. Not elegant, but....it works. 

 

Thanks,


Greg