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]

It looks like the limit is actually 180 characters, not 255 as shown on the page that @Marcelo Branco has linked to.

The only workaround that comes to mind it to use named ranges instead of sheet name and cell range in the formula.

Sub test()
With ActiveWorkbook.Names
    .Add Name:="SubArea", RefersTo:="=Employees!C4:C5000"
    .Add Name:="dept", RefersTo:="=Employees!H4:H5000"
    .Add Name:="qtr", RefersTo:="=Employees!K4:N5000"
End With
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)")
End Sub

 

[288enzo]

It looks like the limit is actually 180 characters, not 255 as shown on the page that @Marcelo Branco has linked to.

The only workaround that comes to mind it to use named ranges instead of sheet name and cell range in the formula.

Sub test()
With ActiveWorkbook.Names
    .Add Name:="SubArea", RefersTo:="=Employees!C4:C5000"
    .Add Name:="dept", RefersTo:="=Employees!H4:H5000"
    .Add Name:="qtr", RefersTo:="=Employees!K4:N5000"
End With
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)")
End Sub

Workbook names is new to me, seems similar to String which is what I was using. I made the changes to my formula, and Bingo!

Thank you very much.

    With ActiveWorkbook.Names
    .Add Name:="SubArea", RefersTo:="=Employees!$C$4:$C$5000"
    .Add Name:="dept", RefersTo:="=Employees!$H$4:$H$5000"
    .Add Name:="qtr", RefersTo:="=Employees!$K$4:$N$5000"
    End With
    
    Range("B4").FormulaArray = "=ROUNDDOWN(1-((SUM(--(MMULT(--IF(dept=""SA"",(qtr=""N""),0),TRANSPOSE(COLUMN(qtr)^0))>0)))/(COUNTIFS(dept,""SA""))),2)"

 

[288enzo]

It looks like the limit is actually 180 characters, not 255 as shown on the page that @Marcelo Branco has linked to.

The only workaround that comes to mind it to use named ranges instead of sheet name and cell range in the formula.

Sub test()
With ActiveWorkbook.Names
    .Add Name:="SubArea", RefersTo:="=Employees!C4:C5000"
    .Add Name:="dept", RefersTo:="=Employees!H4:H5000"
    .Add Name:="qtr", RefersTo:="=Employees!K4:N5000"
End With
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)")
End Sub

BTW where did you come up with 180 characters. I read both articles and they both mention 255. Is it my version of Office? Thank you

 

[jasonb75]

BTW where did you come up with 180 characters.

Trial and error. I had tested with a shorter version that worked fine (single cell references for each range). Given that the only difference between it and yours was the length of the formula I just made changes to the ranges and sheetnames to reduce the character count until it worked.