FormulaArray longer than 225 Characters Pasted into Table

Hi Experts, I'm experiencing difficulty getting my array formulas ranging from 270 characters to 4997 characters long to paste into a table using .FormulaArray =

I've attached a sample workbook.
According to LINK its because my array formulas are longer than 255 characters. However, I can't get the solution on that page or any of the ones in the comments working... does it have something to do with attempting to paste into a table? Is there a workaround?
FormulaArray-problem.xlsm
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
the array formula you are trying to enter is not correct.

can you please upload a correct file that has the table of Historical Global FH and all other tables that involved in the formula?   you formula also is missing parenthesis
-PolakAuthor Commented:
Hi JimJam,

Here's an updated sample book that will make everything work if it were just entered via the sheet instead of VBA.
FormulaArray-problemv2.xlsm
ProfessorJimJamMicrosoft Excel ExpertCommented:
@Polak,

it is obvious the char of array formula is above limit.

you can use the workaround of range.replace or with named range that is described in detail in this blog
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

-PolakAuthor Commented:
Hi JimJam, yes as I described in my original question, I've tried that solution and several in the comments section of that blog to no avail. Here is my attempt....
FormulaArray-problemv3.xlsm
Rgonzo1971Commented:
Hi,

pls try

Public Sub LongArrayFormula()
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    theFormulaPart1 = "=SUM(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers],[Historical Global FH]:[Historical Global FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*X_X_X)))"
    theFormulaPart2 = "TableGlobalFH[[Historical Global FH]:[Historical Global FH]]"
    With Range("Table1[Testing]")
        .Select
        ActiveCell.FormulaArray = theFormulaPart1
        .Replace "X_X_X", theFormulaPart2
    End With
    
End Sub

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
@ Rgonzo that worked like a charm.... until I tried the same method on my 5000 Character long array formula. I'm getting a Type 13 Mismatch error on the .Replace line.

I tried to troubleshoot a bit and what I discovered was If I try and replace "X_X_X" with 6000 characters of the letter A I get the same Type 13 Mismatch error. I termed those 6000 letter-As theFormulaPart2 below

theformulaPart3 in the code below is the real 5000 character long array formula that I'm trying to replace.
Public Sub LongArrayFormula()
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    Dim theFormulaPart3 As String
    theFormulaPart1 = "=SUM(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers],[Historical Global FH]:[Historical Global FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*X_X_X)))"
    theFormulaPart2 = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" & _
    "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" & _
    "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" & _
    "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" & _
    "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" & _
    "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
        
    theFormulaPart3 = "IFERROR(IF(NeworUsed=""Used"", IF(OR(SelectedCONCATPercentileMethod=""Branch 50th Percentile"", SelectedCONCATPercentileMethod=""Branch 80th Percentile""),SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(TableBranchFH[[#Headers],[Branch 50th Percentile]:[Branch 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableBranchFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)))*(TableBranchFH[[Branch 50th Percentile]:[Branch 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableBranchFH[[#Headers], [Historical Branch FH]:[Historical Branch FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableBranchFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)))*(TableBranchFH[[Historical Branch FH]:[Historical Branch FH]])))))" & _
    "/SUM(MMULT(ISNUMBER(MATCH(TableBranchFH[[#Headers], [Historical Branch FH]:[Historical Branch FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableBranchFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)))*(TableBranchFH[[Historical Branch FH]:[Historical Branch FH]])))), IF(OR(SelectedCONCATPercentileMethod=""Global 50th Percentile"", SelectedCONCATPercentileMethod=""Global 80th Percentile"")," & _
    "SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers],[Global 50th Percentile]:[Global 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*(TableGlobalFH[[Global 50th Percentile]:[Global 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers], [Historical Global FH]:[Historical Global FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*(TableGlobalFH[[Historical Global FH]:[Historical Global FH]])))))" & _
    "/SUM(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers], [Historical Global FH]:[Historical GlobalFH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*(TableGlobalFH[[Historical Global FH]:[Historical Global FH]])))),IF(OR(SelectedCONCATPercentileMethod=""Region 50th Percentile"", SelectedCONCATPercentileMethod=""Region 80th Percentile"")," & _
    "SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(TableRegionFH[[#Headers],[Region 50th Percentile]:[Region 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableRegionFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableRegionFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)))*(TableRegionFH[[Region 50th Percentile]:[Region 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableRegionFH[[#Headers], [Historical Region FH]:[Historical Region FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableRegionFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableRegionFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)))*(TableRegionFH[[Historical Region FH]:[Historical Region FH]])))))" & _
    "/SUM(MMULT(ISNUMBER(MATCH(TableRegionFH[[#Headers], [Historical Region FH]:[Historical Region FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableRegionFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableRegionFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)))*(TableRegionFH[[Historical Region FH]:[Historical Region FH]])))),IF(OR(SelectedCONCATPercentileMethod=""Local 50th Percentile"", SelectedCONCATPercentileMethod=""Local 80th Percentile"")," & _
    "SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[Local 50th Percentile]:[Local 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*(TableAircraftCostData[[Local 50th Percentile]:[Local 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers], [Historical Local FH]:[Historical Local FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*(TableAircraftCostData[[Historical Local FH]:[Historical Local FH]])))))" & _
    "/SUM(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers], [Historical Local FH]:[Historical Local FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*(TableAircraftCostData[[Historical Local FH]:[Historical Local FH]])))),"""")))),""""),"""")"
    
    With Range("Table1[Testing]")
        .Select
        ActiveCell.FormulaArray = theFormulaPart1
        .Replace "X_X_X", theFormulaPart2
    End With
End Sub

Open in new window

-PolakAuthor Commented:
Okay just figured something out....
Seems that length of the replace value must be 255 characters or shorter or it will trigger the type 13 mismatch error.  

Is this because we are diming each part of the formula as a String and the limit on a String is 255 characters as well?

If so, is there a workaround for a formula like theFormulaPart3 in the code above? Perhaps something like theFormulaPart2 + theFormulaPart3 + theFormulaPart4 making each string 255 characters? I've tired that on my own but it doesn't seem to work.
Rgonzo1971Commented:
You should try to replace in different stages

your Formula =Sum(XXX1*XXX2*XXX3*XXX4*XXX5*XXX6*XXX7) and replace each XXXx with a string with length less than 255

Regards
-PolakAuthor Commented:
I found breaking the formula into its working parts impossible, because each "working-component" of the formula is longer than 255 characters. Instead, I separated the formula into 20 255 character strings and wrote this....:
Sub ResetMoUtilization()
Application.Calculation = xlCalculationManual
    Dim theFormulaPart1, theFormulaPart2, theFormulaPart3, theFormulaPart4, theFormulaPart5, theFormulaPart6, theFormulaPart7, theFormulaPart8, theFormulaPart9, theFormulaPart10, theFormulaPart11, theFormulaPart12, theFormulaPart13, theFormulaPart14, theFormulaPart15, theFormulaPart16, theFormulaPart17, theFormulaPart18, theFormulaPart19, theFormulaPart20, theFormulaPart21 As String
    
    theFormulaPart1 = "=IFERROR(XXX01XXX02XXX03XXX04XXX05XXX06XXX07XXX08XXX09XXX10XXX11XXX12XXX13XXX14XXX15XXX16XXX17XXX18XXX19XXX20,"""")"
    theFormulaPart2 = "IF(NeworUsed=""Used"", IF(OR(SelectedCONCATPercentileMethod=""Branch 50th Percentile"", SelectedCONCATPercentileMethod=""Branch 80th Percentile""),SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(TableBranchFH[[#Headers],[Branch 50th Percentile]:[Branch 80th Pe"
    theFormulaPart3 = "rcentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableBranchFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)))*(TableBranchFH[[Branch 50th Pe"
    theFormulaPart4 = "rcentile]:[Branch 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableBranchFH[[#Headers], [Historical Branch FH]:[Historical Branch FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableBranchFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableBranchFH[FY],Selec"
    theFormulaPart5 = "tedFY,0)))*(ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)))*(TableBranchFH[[Historical Branch FH]:[Historical Branch FH]])))))/SUM(MMULT(ISNUMBER(MATCH(TableBranchFH[[#Headers], [Historical Branch FH]:[Historical Branch FH]],Sele"
    theFormulaPart6 = "ctedCPFHCalcMethod,0))+0,TRANSPOSE((TableBranchFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)))*(TableBranchFH[[Historical Branch FH]:[Historical Bra"
    theFormulaPart7 = "nch FH]])))), IF(OR(SelectedCONCATPercentileMethod=""Global 50th Percentile"", SelectedCONCATPercentileMethod=""Global 80th Percentile""),SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers],[Global 50th Percentile]:[Global 80th Percentile]],Select"
    theFormulaPart8 = "edCONCATPercentileMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*(TableGlobalFH[[Global 50th Percentile]:[Global 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers], [His"
    theFormulaPart9 = "torical Global FH]:[Historical Global FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*(TableGlobalFH[[Historical Global FH]:[Historical Global FH]])))))/SUM(MMULT(ISNU"
    theFormulaPart10 = "MBER(MATCH(TableGlobalFH[[#Headers], [Historical Global FH]:[Historical Global FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*(TableGlobalFH[[Historical Global FH]:[H"
    theFormulaPart11 = "istorical Global FH]])))),IF(OR(SelectedCONCATPercentileMethod=""Region 50th Percentile"", SelectedCONCATPercentileMethod=""Region 80th Percentile""),SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(TableRegionFH[[#Headers],[Region 50th Percentile]:[Region 80th Percent"
    theFormulaPart12 = "ile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableRegionFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableRegionFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)))*(TableRegionFH[[Region 50th Perce"
    theFormulaPart13 = "ntile]:[Region 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableRegionFH[[#Headers], [Historical Region FH]:[Historical Region FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableRegionFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableRegionFH[FY],Selected"
    theFormulaPart14 = "FY,0)))*(ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)))*(TableRegionFH[[Historical Region FH]:[Historical Region FH]])))))/SUM(MMULT(ISNUMBER(MATCH(TableRegionFH[[#Headers], [Historical Region FH]:[Historical Region FH]],Selec"
    theFormulaPart15 = "tedCPFHCalcMethod,0))+0,TRANSPOSE((TableRegionFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableRegionFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)))*(TableRegionFH[[Historical Region FH]:[Historical Re"
    theFormulaPart16 = "gion FH]])))),IF(OR(SelectedCONCATPercentileMethod=""Local 50th Percentile"", SelectedCONCATPercentileMethod=""Local 80th Percentile""),SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[Local 50th Percentile]:[Local 80th Percentile]],Se"
    theFormulaPart17 = "lectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*(TableAircraftC"
    theFormulaPart18 = "ostData[[Local 50th Percentile]:[Local 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers], [Historical Local FH]:[Historical Local FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISN"
    theFormulaPart19 = "UMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*(TableAircraftCostData[[Historical Local FH]:[Historical Local FH]])))))/SUM(MMULT(ISNUMBER(MATCH(TableAircraftCos"
    theFormulaPart20 = "tData[[#Headers], [Historical Local FH]:[Historical Local FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],T"
    theFormulaPart21 = "ableFHAnalysisEngine[OpLocationCode],0)))*(TableAircraftCostData[[Historical Local FH]:[Historical Local FH]])))),"""")))),"""")"
    
    With Range("Table1[Testing]")
        .Select
        ActiveCell.FormulaArray = theFormulaPart1
        .Replace "XXX01", theFormulaPart2
        .Replace "XXX02", theFormulaPart3
        .Replace "XXX03", theFormulaPart4
        .Replace "XXX04", theFormulaPart5
        .Replace "XXX05", theFormulaPart6
        .Replace "XXX06", theFormulaPart7
        .Replace "XXX07", theFormulaPart8
        .Replace "XXX08", theFormulaPart9
        .Replace "XXX09", theFormulaPart10
        .Replace "XXX10", theFormulaPart11
        .Replace "XXX11", theFormulaPart12
        .Replace "XXX12", theFormulaPart13
        .Replace "XXX13", theFormulaPart14
        .Replace "XXX14", theFormulaPart15
        .Replace "XXX15", theFormulaPart16
        .Replace "XXX16", theFormulaPart17
        .Replace "XXX17", theFormulaPart18
        .Replace "XXX18", theFormulaPart19
        .Replace "XXX19", theFormulaPart20
        .Replace "XXX20", theFormulaPart21
    End With
Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

I thought it would work, but stepping though it appears that excel won't allow the replacement of an XXX## value because each theFormulaPart## will not evaluate independently of the other parts, (turning off AutoCalculation does not help). Initially, if instead of replacing "XXX01" you replace  "=IFERROR(XXX01" then you can get the entire array into the table as a text value; but then trying to make that text value your array gets us right back to where we started.

So if i'm interpreting what's happening correctly, is there anyway to make this work if each "working-component" of my very long array needs more than 255 characters to evaluate?  

Updated sample workbook is attached.
FormulaArray-problemv4.xlsm
Rgonzo1971Commented:
the Formula does have to be appropriate at all times so like this it won't work

Could you send a worksheet wit the formula already entered
-PolakAuthor Commented:
@ Rgonzo

See the attached. I've shortened the array formula to only include the first two IF statement (as opposed to all four) to simplify the amount of dummy data/tables I had to create. Still 2300 characters long and I should be able to adapt any workaround you provide easily to the real workbook.

Are you sure there isn't any excel function that we can use in combination with FormulaArray that would allow us to enter in all the strings we wanted then once everything has been replaced change the function? I was thinking of trying to use the "=Text(" function. Then after all the formula parts are entered replacing the Text function with the real formula's IFERROR function........
FormulaArray-problemv5.xlsm
Rgonzo1971Commented:
You could try the Sendkeys method

Not a favorite but a practical solution for you
Sub MAcro()
LongFormula = "=IFERROR(IF(NeworUsed=""Used"", IF(OR(SelectedCONCATPercentileMethod=""Branch 50th Percentile"",SelectedCONCATPercentileMethod=""Branch 80th Percentile""),SUMPRODUCT" & _
"(--(MMULT(ISNUMBER(MATCH(TableBranchFH[[#Headers],[Branch 50th Percentile]:[Branch 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableBranchFH[Asset]=" & _
"[@[Aircraft Type]])*(ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)))*(TableBranchFH[[Branch 50th Percentile]" & _
":[Branch 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableBranchFH[[#Headers], [Historical Branch FH]:[Historical Branch FH]],SelectedCPFHCalcMethod,0))+0," & _
"TRANSPOSE((TableBranchFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)))" & _
"*(TableBranchFH[[Historical Branch FH]:[Historical Branch FH]])))))/SUM(MMULT(ISNUMBER(MATCH(TableBranchFH[[#Headers], [Historical Branch FH]:[Historical Branch FH]],SelectedCPFH" & _
"CalcMethod,0))+0,TRANSPOSE((TableBranchFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableBranchFH[Branch]," & _
"TableFHAnalysisEngine[OpBranch],0)))*(TableBranchFH[[Historical Branch FH]:[Historical Branch FH]])))), IF(OR(SelectedCONCATPercentileMethod=""Global 50th Percentile"", " & _
"SelectedCONCATPercentileMethod=""Global 80th Percentile""),SUMPRODUCT(--(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers],[Global 50th Percentile]:[Global 80th Percentile]]," & _
"SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*(TableGlobalFH" & _
"[[Global 50th Percentile]:[Global 80th Percentile]])))),(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers], [Historical Global FH]:[Historical Global FH]]," & _
"SelectedCPFHCalcMethod,0))+0,TRANSPOSE((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*(TableGlobalFH[[Historical Global FH]:" & _
"[Historical Global FH]])))))/SUM(MMULT(ISNUMBER(MATCH(TableGlobalFH[[#Headers], [Historical Global FH]:[Historical Global FH]],SelectedCPFHCalcMethod,0))+0,TRANSPOSE" & _
"((TableGlobalFH[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)))*(TableGlobalFH[[Historical Global FH]:[Historical Global FH]])))),"""")),""""),"""")"
With Range("Table1[Testing]")
    .Select
    ActiveCell.Select
    ActiveCell.Formula = LongFormula
    DoEvents
    Application.SendKeys "{F2}^+~"
End With

End Sub

Open in new window

Regards
-PolakAuthor Commented:
Not my favorite either, although I feel somewhat validated that I found a problem that sendkeys is actually appropriate. Thanks for the solution on the shorter arrayformula and making me understand that each component of what gets replaced must work independently.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.