Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-820546%22%20slang%3D%22en-US%22%3EMy%20formula%20in%20VBA%20is%20too%20long%3F%20Is%20this%20really%20a%20thing%3F%20How%20to%20fix%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820546%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20section%20in%20my%20code%20which%20uses%20a%20formula.%20It%20works%20fine%20in%20excel%20but%20when%20I%20put%20it%20in%20VBA%2C%20it%20will%20not%20put%20the%20formula%20in%20the%20cell%20(i.e.%20cell%20B2).%20The%20coding%20should%20be%20correct%20as%20I%20simply%20recorded%20a%20macro%20and%20copied%20that%20code%20into%20the%20VBA%20code%20(which%20I've%20done%20before%20without%20issue%20with%20a%20smaller%20version%20of%20the%20formula).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20step%20through%20that%20section%20of%20the%20code%2C%20I%20get%20this%20error%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22unable%20to%20set%20the%20formulaarray%20property%20of%20the%20range%20class%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20google%20search%20suggests%20that%20there%20is%20a%20255%20character%20limit%20to%20formulas...which%20I%20certainly%20exceed.%20Is%20this%20really%20a%20thing%3F%20Is%20there%20a%20way%20around%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECode%20follows%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThough%20it%20seems%20crazy%2C%20I%20have%2C%20like%2C%2012%20of%20these%20formulas%20in%20the%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eusing%3A%20Microsoft%20Office%20365%20ProPlus%3C%2FP%3E%3CP%3E------------------------------%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESelection.FormulaArray%20%3D%20_%3CBR%20%2F%3E%22%3DIFERROR(IF(ISNA(VLOOKUP(RC1%2CUndilutedPLUS!C1%3AC18%2C14%2CFALSE))%2C(IF(INDEX(DilutedPLUS!C%5B12%5D%2CMATCH(RC%5B-1%5D%26amp%3B1%2CDilutedPLUS!C%5B-1%5D%26amp%3BDilutedPLUS!C%5B12%5D%2C0))%3D1%2CINDEX(DilutedPLUS!C%2CMATCH(RC%5B-1%5D%26amp%3B1%2CDilutedPLUS!C%5B-1%5D%26amp%3BDilutedPLUS!C%5B12%5D%2C0))%2CINDEX(DilutedPLUS!C%2CMATCH(RC%5B-1%5D%26amp%3B0%2CDilutedPLUS!C%5B-1%5D%26amp%3BDilutedPLUS!C%5B12%5D%2C0))))%2C(IF(VLOOKUP(RC1%2CUndilutedPLUS!C1%3AC18%2C14%2CFALSE)%3D1%2CVLOOKUP(RC1%2CUndiluted%22%20%26amp%3B%20_%3CBR%20%2F%3E%22PLUS!C1%3AC18%2C2%2CFALSE)%2CIF(VLOOKUP(RC1%2CDilutedPLUS!C1%3AC18%2C14%2CFALSE)%3D1%2CVLOOKUP(RC1%2CDilutedPLUS!C1%3AC18%2C2%2CFALSE)%2C(IF(INDEX(DilutedPLUS!C%5B12%5D%2CMATCH(RC%5B-1%5D%26amp%3B1%2CDilutedPLUS!C%5B-1%5D%26amp%3BDilutedPLUS!C%5B12%5D%2C0))%3D1%2CINDEX(DilutedPLUS!C%2CMATCH(RC%5B-1%5D%26amp%3B1%2CDilutedPLUS!C%5B-1%5D%26amp%3BDilutedPLUS!C%5B12%5D%2C0))%2CINDEX(DilutedPLUS!C%2CMATCH('Final%20Data'!RC%5B-1%5D%26amp%3B0%2CDilutedPLUS!C%5B-1%5D%26amp%3BDilutedPLUS!C%5B12%5D%2C0))))))))%2CVLOOKUP%22%20%26amp%3B%20_%3CBR%20%2F%3E%22(RC1%2CUndilutedPLUS!C1%3AC18%2C2%2CFALSE))%22%20%26amp%3B%20_%3CBR%20%2F%3E%22%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-820546%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821069%22%20slang%3D%22en-US%22%3ERe%3A%20My%20formula%20in%20VBA%20is%20too%20long%3F%20Is%20this%20really%20a%20thing%3F%20How%20to%20fix%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821069%22%20slang%3D%22en-US%22%3E%3CP%3ESo%2C%20if%20there's%20an%20easy%20way%2C%20let%20me%20know.%20I%20don't%20want%20to%20get%20into%20breaking%20the%20formula%20down%20into%204%20formulas%20and%20stitching%20it%20back%20together.%20Twelve%20times.%20And%20then%20someday%20I'll%20need%20to%20modify%20it%20and%20have%20to%20do%20it%20all%20again!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20meanwhile%2C%20I%20think%20I%20can%20just%20have%20a%20dreaded%20%22Formulas%22%20tab%2C%20and%20have%20VBA%20copy%2Fpast%20it%20where%20I%20need%20to%20and%20autofill%20the%20rows%20to%20the%20end.%20Not%20elegant%2C%20but....it%20works.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3C%2FLINGO-BODY%3E
gms4b
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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies