question of entering array formula in VBA

[shuoshuo]

Hello all,
Does anyone have any idea what's wrong here?

I was trying to transfer worksheet array formula into VBA. I wanted to use quartiles to draw box plot. I recorded a macro and press F2 then "Ctrl+Shift + Enter"in the worksheet cells to transfer the worksheet formula into VBA. But it says:

Run-time error '1004':
Unable to set FormulaArray property of the Range class.

My VBA code from recording macro is as follows:

Range("D33").Select
Selection.FormulaArray = _
"=IFERROR(IF(R6C2=""See result for exact number of units"",QUARTILE(IF(Data!C14=IF(R2C2=""All"",Data!C14,R2C2),IF(Data!C15=IF(R3C2=""All"",""<>"",R3C2),IF(Data!C16=IF(R4C2=""All"",Data!C16,R4C2),IF(Data!C4=R5C2,IF(Data!C5=R7C2,Data!C12))))),1),QUARTILE(IF(Data!C14=IF(R2C2=""All"",Data!C14,R2C2),IF(Data!C15=IF(R3C2=""All"",""<>"",R3C2),IF(Data!C16=IF(R4C2=""All"",Data" & _
"!C16,R4C2),IF(Data!C4=R5C2,IF(Data!C5>=R29C1,IF(Data!C5<=R29C2,Data!C12)))))),1)),""N/A"")" & _
""

Would appreciate very much if anyone can tell me what's going wrong here.

 

[pgc01]

Hi
Welcome to the board

YOu cannot enter directly an array formula with more than 255 characters with vba.
You can enter a smaller one with the same structure and then perform the necessary replacements.

I posted here a solution:

https://www.mrexcel.com/forum/excel-questions/687125-long-array-formulas-vba.html

 

[shuoshuo]

Hi
Welcome to the board

YOu cannot enter directly an array formula with more than 255 characters with vba.
You can enter a smaller one with the same structure and then perform the necessary replacements.

I posted here a solution:

https://www.mrexcel.com/forum/excel-questions/687125-long-array-formulas-vba.html

Hi,

Thanks so much for your answer! I now know where the problem is. But after I tried to split the formula in this way, it returned the value of "N/A", which is what returns under the IFERROR case. Could you have a look at what is wrong here? Thank you very much!

My code is as below:

Range("D32").Select
Dim FormulaPart1, FormulaPart2, FormulaPart3, FormulaPart4, FormulaPart5, FormulaPart6, FormulaPart7, FormulaPart8, FormulaPart9, FormulaPart10, FormulaPart11, FormulaPart12, FormulaPart13 As String
Selection.FormulaArray = _
"=IFERROR(IF(R6C2=""See result for exact number of units"",QUARTILE(FormulaPart1,1),QUARTILE(FormulaPart5,1)),""N/A"")" & _
""
FormulaPart1 = "=IF(Data!C14=IF(R2C2=""All"",Data!C14,R2C2),FormulaPart2)"
FormulaPart2 = "=IF(Data!C15=IF(R3C2=""All"",Data!C15,R3C2),FormulaPart3)"
FormulaPart3 = "=IF(Data!C16=IF(R4C2=""All"",Data!C16,R4C2),FormulaPart4)"
FormulaPart4 = "=IF(Data!C4=R5C2,IF(Data!C5=R7C2,Data!C9))"
FormulaPart5 = "=IF(Data!C14=FormulaPart9,FormulaPart6)"
FormulaPart6 = "=IF(Data!C15=FormulaPart10,FormulaPart7)"
FormulaPart7 = "=IF(Data!C16=FormulaPart11,FormulaPart8)"
FormulaPart8 = "=IF(Data!C4=R5C2,FormulaPart12)"
FormulaPart9 = "=IF(R2C2=""All"",Data!C14,R2C2)"
FormulaPart10 = "=IF(R3C2=""All"",Data!C15,R3C2)"
FormulaPart11 = "=IF(R4C2=""All"",Data!C16,R4C2)"
FormulaPart12 = "=IF(Data!C5>=R29C1,FormulaPart13)"
FormulaPart13 = "=IF(Data!C5<=R29C2,Data!C9)"

 

[pgc01]

Hi

I did a quick test and it seems it worked OK.

I split the formula at the Quartiles.

Try:

Sub test()
Dim sQuartile1 As String, sQuartile2 As String
Dim r As Range
Dim vReferenceStyle As Variant

Set r = Range("D33")

sQuartile1 = "QUARTILE(IF(Data!C14=IF(R2C2=""All"",Data!C14,R2C2),IF(Data!C15=IF(R3C2=""All"",""<>"",R3C2),IF(Data!C16=IF(R4C2=""All"",Data!C16,R4C2),IF(Data!C4=R5C2,IF(Data!C5=R7C2,Data!C12))))),1)"
sQuartile2 = "QUARTILE(IF(Data!C14=IF(R2C2=""All"",Data!C14,R2C2),IF(Data!C15=IF(R3C2=""All"",""<>"",R3C2),IF(Data!C16=IF(R4C2=""All"",Data" & "!C16,R4C2),IF(Data!C4=R5C2,IF(Data!C5>=R29C1,IF(Data!C5<=R29C2,Data!C12)))))),1)"

vReferenceStyle = Application.ReferenceStyle ' store reference style
Application.ReferenceStyle = xlR1C1

Range("D33").FormulaArray = _
    "=IFERROR(IF(R6C2=""See result for exact number of units"",1111,2222),""N/A"")"

r.Replace "1111", sQuartile1, LookAt:=xlPart
r.Replace "2222", sQuartile2, LookAt:=xlPart

Application.ReferenceStyle = vReferenceStyle ' restore reference style

End Sub

 

[shuoshuo]

It works! Thanks so much!!!!!

Hi

I did a quick test and it seems it worked OK.

I split the formula at the Quartiles.

Try:

Sub test()
Dim sQuartile1 As String, sQuartile2 As String
Dim r As Range
Dim vReferenceStyle As Variant

Set r = Range("D33")

sQuartile1 = "QUARTILE(IF(Data!C14=IF(R2C2=""All"",Data!C14,R2C2),IF(Data!C15=IF(R3C2=""All"",""<>"",R3C2),IF(Data!C16=IF(R4C2=""All"",Data!C16,R4C2),IF(Data!C4=R5C2,IF(Data!C5=R7C2,Data!C12))))),1)"
sQuartile2 = "QUARTILE(IF(Data!C14=IF(R2C2=""All"",Data!C14,R2C2),IF(Data!C15=IF(R3C2=""All"",""<>"",R3C2),IF(Data!C16=IF(R4C2=""All"",Data" & "!C16,R4C2),IF(Data!C4=R5C2,IF(Data!C5>=R29C1,IF(Data!C5<=R29C2,Data!C12)))))),1)"

vReferenceStyle = Application.ReferenceStyle ' store reference style
Application.ReferenceStyle = xlR1C1

Range("D33").FormulaArray = _
    "=IFERROR(IF(R6C2=""See result for exact number of units"",1111,2222),""N/A"")"

r.Replace "1111", sQuartile1, LookAt:=xlPart
r.Replace "2222", sQuartile2, LookAt:=xlPart

Application.ReferenceStyle = vReferenceStyle ' restore reference style

End Sub

 

[pgc01]

You're welcome. Thanks for the feedback.