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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies