Unholy Mess of a Formula, Please Help!

[JeffK627]

I have the following nightmare (line breaks added for legibility):

Sheet2.Range(.Cells(5, i + 11), .Cells(lastRow, i + 11)).Formula = _
"=CHOOSE(MATCH(RC4,""No Category Defined"",
""No MCO"",
""MCO – PB thru FFS"",
""MCO – PB thru MCO – Sub – Sep"",
""MCO – PB thru MCO – Sub – Comb"",
""MCO – PB thru MCO – Not Sub"",
""MCO – PB through FFS & MCO – Sub – Sep"",
""MCO – PB through FFS & MCO – Sub – Comb"",
""MCO – PB through FFS & MCO – Not Sub "",0), 
0, RC[-4], RC[-4], RC[-4], 
RC[-5] * AVERAGE(IF(C4=""MCO – PB thru MCO – Sub – Sep"", C[-2]:C[-2])), 
(RC[-5] * (AVERAGE(IF(C4=""MCO – PB thru MCO – Sub – Sep"", C[-2]:C[-2])))) / (1-(AVERAGE(IF(C4=""MCO – PB thru MCO – Sub – Sep"", C[-2]:C[-2])))), 
RC[-4], 
RC[-5] * (AVERAGE(IF(C4=""MCO – PB through FFS & MCO – Sub – Sep"", C[-2]:C[-2]))), 
(RC[-5] * (AVERAGE(IF(C4=""MCO – PB through FFS & MCO – Sub – Sep"", C[-2]:C[-2])))) / (1-(AVERAGE(IF(C4=""MCO – PB through FFS & MCO – Sub – Sep"", C[-2]:C[-2])))))"

The idea is to enter a different formula into a specified column of a spreadsheet depending on the value in the corresponding row in column D.

The above would work fine, except that some of the formulas are simple formulas and some are array formulas. The array formulas cause an error when the VBA code gets to this line. I tried using "Sheet2.Range(.Cells(5, i + 11), .Cells(lastRow, i + 11)).FormulaArray =" but that also threw an error. I also tried enclosing the array formulas in curly brackets but that didn't work either. Can anyone help?

 

[Scott Huish]

You can't define a formula for the cell within a formula. To do that you need to examine each possibililty and execute a different .Formula = or .Value statement as appropriate.

I would also use VBA find instead of MATCH and then do a Select Case based on the result.

 

[JeffK627]

I'm trying to avoid looping through all the cells in VBA because there are literally tens of thousands of rows in this thing and I don't want the performance hit.

 

[AlphaFrog]

AutoFilter on column D for the different values and then place the specific formula for that filtered value in the visible cells of the other columns.

You would loop the autofilter values in column D but place the formulas en masse in the visible results.

 

[Fazza]

I suspect - with tens of thousands of rows - solutions using formulas, especially array formulas, will be slow. If you find it is slow, I suggest you consider non-formula approaches using SQL. So a query instead of a large worksheet of formulas: maybe a query table or VBA/ADO or even pivot table.