Contributor

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

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

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

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