How to make Long FormulaArray macro to work? :(

[dellzy]

Dear Excel Expertise,

I had an error which I googled showing the meaning is that my formula is too long (>255 chars). I tried to cut them into lines like below but I still got the same error. Can anybody be kind enough to correct my formula, please? I have tried to troubleshoot for hours..

    Selection.FormulaArray = _
        "=IFERROR(IF(RC7=MIN(IF(INDIRECT(""A2:A""&COUNTA(C[-7]))=RC1," & _
        "IF(INDIRECT(""F2:F""&COUNTA(C[-7]))=RC6,INDIRECT(""G2:G""&COUNTA(C[-7])),""""),""""))," & _
        "IF(OR(OR(RC6=""NGIN_SERVICE"",RC6=""NGIN_PBO"",RC6=""NGIN_MS"")," & _
        "OR(RC6=""NGIN_DISPUTE-NON_PBO"",RC6=""NGIN_DISPUTE_NON_PBO"",RC6=""HQ_PREPAID"")," & _
        "RC6=""HQ_CAS""),""Huawei"",""""),""""),"""")"

Thank you in advance.

DZ

 

[pgc01]

Hi

I posted about this here (also has an example):

http://www.mrexcel.com/forum/excel-...array-formulas-visual-basic-applications.html

See if it helps.

 

[pgc01]

This is a test with your formula.

The formula is not much bigger than the limit, it has 300+ characters. Just replacing the last OR() will be enough to make it less than 255 characters.

In this test I replaced the last OR() with 9999, and then used Range.Replace to put the OR() back.

Remember that, like I said in the link I posted, we have to make sure that the formula syntax is respected.

If you check the formula with the 9999, you'll see that the formula syntax is correct and that the cell accepts the formula OK.

Try:

Sub Test()
Dim r As Range
Dim sFormula0 As String, sFormula1 As String
Dim InitialReferenceStyle As XlReferenceStyle

Set r = Range("J10")

sFormula0 = _
        "=IFERROR(IF(RC7=MIN(IF(INDIRECT(""A2:A""&COUNTA(C[-7]))=RC1," & _
        "IF(INDIRECT(""F2:F""&COUNTA(C[-7]))=RC6,INDIRECT(""G2:G""&COUNTA(C[-7])),""""),""""))," & _
        "IF(OR(OR(RC6=""NGIN_SERVICE"",RC6=""NGIN_PBO"",RC6=""NGIN_MS"")," & _
        9999 & "," & _
        "RC6=""HQ_CAS""),""Huawei"",""""),""""),"""")"

sFormula1 = "OR(RC6=""NGIN_DISPUTE-NON_PBO"",RC6=""NGIN_DISPUTE_NON_PBO"",RC6=""HQ_PREPAID"")"

' set reference style to R1C1
InitialReferenceStyle = Application.ReferenceStyle
Application.ReferenceStyle = xlR1C1

r.FormulaArray = sFormula0

r.Replace What:="9999", Replacement:=sFormula1, LookAt:=xlPart

' reset the reference style to the previous style
Application.ReferenceStyle = InitialReferenceStyle

End Sub