Aug 23 2019 11:07 AM - edited Aug 23 2019 11:33 AM
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))" & _
""
Aug 23 2019 05:35 PM
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