Combining working .FormulaArray to working formula

[288enzo]

I have two formulas that I am trying to put together. They work on their own, but not when put together. What am I missing?

Range("B4").FormulaArray = "=(SUM(--(MMULT(--IF((" & subarea & "=RIGHT(A4,3))*(" & dept & "=""SA""),(" & qtr & "=""N""),0),TRANSPOSE(COLUMN(" & qtr & ")^0))>0)))"

and

Range("B7") = "=ROUNDDOWN(1-(B4/(COUNTIFS(" & dept & ",""SA""," & subarea & ",RIGHT(A4,3)))),2)"

I would have thought this -

Range("B7").FormulaArray = "=ROUNDDOWN(1-((SUM(--(MMULT(--IF((" & subarea & "=RIGHT(A4,3))*(" & dept & "=""SA""),(" & qtr & "=""N""),0),TRANSPOSE(COLUMN(" & qtr & ")^0))>0)))/(COUNTIFS(" & dept & ",""SA""," & subarea & ",RIGHT(A4,3)))),2)"

But I get "Unable to set FormulaArray property of the Range class"

Thank you

 

[jasonb75]

Best guess would be that one of the variables is not valid. Try removing the = sign at the start of the formula so that it is entered into the cell as a text string, or using debug.print to show it in the immediate pane. That might make it easier to find the problem.

 

[288enzo]

Best guess would be that one of the variables is not valid. Try removing the = sign at the start of the formula so that it is entered into the cell as a text string, or using debug.print to show it in the immediate pane. That might make it easier to find the problem.

I tried that, everything looks right. On their own, both formulas work perfectly. When combined, they don't. I don't understand why this is.

 

[jasonb75]

I tried that, everything looks right.

It might look right but if it actually was right then it would work, it might not give you the result you expect but it would still enter into the cell.

edit:- Try wrapping the whole formula in IFERROR so that it will return a blank if the formula evaluates to any kind of error.

 

[Marcelo Branco]

Not sure if this the problem, but...

See

Range.FormulaArray property (Excel)

Office VBA reference topic

docs.microsoft.com

The FormulaArray property also has a character limit of 255.

M.

 

[288enzo]

Not sure if this the problem, but...

See

Range.FormulaArray property (Excel)

Office VBA reference topic

docs.microsoft.com

The FormulaArray property also has a character limit of 255.

M.

That could be it, I'm at 257.

 

[Marcelo Branco]

That could be it, I'm at 257.

Take a look at

Entering Long Array Formulas in VBA – Daily Dose of Excel

M.

 

[288enzo]

Not sure if this the problem, but...

See

Range.FormulaArray property (Excel)

Office VBA reference topic

docs.microsoft.com

The FormulaArray property also has a character limit of 255.

M.

Got it down to 235, still getting the same error.

 

[Marcelo Branco]

Got it down to 235, still getting the same error.

My guess: Syntax error

M.

 

[288enzo]

Take a look at

Entering Long Array Formulas in VBA – Daily Dose of Excel

M.

Interesting, but I'm under the limit. If I take out the "=" and run it I get

ROUNDDOWN(1-((SUM(--(MMULT(--IF((Employees!C4:C5000=RIGHT(A4,3))*(Employees!H4:H5000="SA"),(Employees!K4:N5000="N"),0),TRANSPOSE(COLUMN(Employees!K4:N5000)^0))>0)))/(COUNTIFS(Employees!H4:H5000,"SA",Employees!C4:C5000,RIGHT(A4,3)))),2)

If I break it up where it does work I have the following:

B4 =

(SUM(--(MMULT(--IF((Employees!C4:C5000=RIGHT(A4,3))*(Employees!H4:H5000="SA"),(Employees!K4:N5000="N"),0),TRANSPOSE(COLUMN(Employees!K4:N5000)^0))>0)))

ROUNDDOWN(1-(B4/(COUNTIFS(Employees!H4:H5000,"SA",Employees!C4:C5000,RIGHT(A4,3)))),2)