Error with Long Array Formula

[AMAS]

Hi everyone,

Someone must have had this problem before and shed some light on it for me. I have a long formula that works fine when typed into the formula bar and works find if denote it as a formula in vba but does not work as an array formula in vba. I have tried splitting it and using the replace function but I just can't seem to make it work. Any idea what I am doing wrong???

This works in Cell P2:

=IF(AND(D2:O2=""),"No RoB assessments performed",
IF(OR(CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2)="no"),"HIGH",
IF(OR(CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2)="High Risk"),"HIGH",
IF(OR(CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2)="UNCLEAR"),"UNCLEAR",
IF(OR(CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2)="Unclear Risk"),"UNCLEAR","LOW")))))

This works in vba if I denote it as .Formula but not .FormulaArray:

With Range("P" & A)
    .Formula = "=IF(AND(RC[-12]:RC[-1]=""""),""No RoB assessments performed""," & Chr(10) & _
        "IF(OR(CHOOSE({1,2,3,4,5,6},RC[-12],RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""no""),""HIGH""," & Chr(10) & _
        "IF(OR(CHOOSE({1,2,3,4,5,6},RC[-12],RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""High Risk""),""HIGH""," & Chr(10) & _
        "IF(OR(CHOOSE({1,2,3,4,5,6},RC[-12],RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""UNCLEAR""),""UNCLEAR""," & Chr(10) & _
        "IF(OR(CHOOSE({1,2,3,4,5,6},RC[-12],RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""Unclear Risk""),""UNCLEAR"",""LOW"")))))"
End With

AMAS

 

[AMAS]

Anyone with experience with this sort of problem?

AMAS

 

[AMAS]

I have been able to isolate the problem. I can replace the last part of the IF statement with giberish (e.g. ""XYZ123"") and replace that with other text using the replace function, but it will not work for formulas only text. How do I replace it with a formula? Does anyone know?

AMAS

 

[shg]

Try this:

Sub demo()
    Const sFrm      As String = _
          "=IF(AND(RC[-12]:RC[-1]=""""),""No RoB assessments performed""," & vbLf & _
          "IF(OR(CHOOSE({1,2,3,4,5,6},RC[-12],RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""no""),""HIGH""," & vbLf & _
          "IF(OR(CHOOSE({1,2,3,4,5,6},RC[-12],RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""High Risk""),""HIGH""," & vbLf & _
          "IF(OR(CHOOSE({1,2,3,4,5,6},RC[-12],RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""UNCLEAR""),""UNCLEAR""," & vbLf & _
          "IF(OR(CHOOSE({1,2,3,4,5,6},RC[-12],RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""Unclear Risk""),""UNCLEAR"",""LOW"")))))"
 
    Debug.Print InsertArrayFormula(Range("M1"), sFrm, xlR1C1)
End Sub
 
Function InsertArrayFormula(r As Range, _
                            sFrm As String, _
                            iRef As XlReferenceStyle, _
                            Optional sFmtNew As String = "") As Boolean
    ' Inserts the array formula sFrm having reference style iRef in r

    Dim sFmtOld     As String
    Dim iRefOld     As XlReferenceStyle
    Dim rSel        As Range
    On Error GoTo Oops
    
    With Application
        Set rSel = ActiveWindow.RangeSelection
        iRefOld = .ReferenceStyle
        .ReferenceStyle = iRef
        .ScreenUpdating = False
 
        With r.Areas(1)
            ' can't put an array formula in cells that are
            ' not either all locked or all unlocked
            .Locked = .Cells(1).Locked
            sFmtOld = .NumberFormat
            .NumberFormat = "@"
            .Value = sFrm
            .NumberFormat = IIf(Len(sFmtNew), sFmtNew, sFmtOld)
            Application.Goto .Cells
        End With
 
        DoEvents
        .SendKeys "{F2}^+~"
        DoEvents
        .ReferenceStyle = iRefOld
        .Goto rSel
        .ScreenUpdating = True
    End With
    
    InsertArrayFormula = True
Oops:
    Exit Function
End Function

 

[AMAS]

Thanks. This is a lot more complex than I had imagined the solution. I can't even get it work (probably a library not installed on my system or something like that). It gives me an error at the second DoEvents and says that the Search String Must Be Specified.

AMAS

 

[shg]

You can't have the VBE open (more accurately, have focus) when you run the code; otherwise you end up in the Object Browser when you send F2 to the VBE window.

 

[AMAS]

You are absolutely right. It works now. I don't have the slightest clue why it doesn't work from the browser window, but hey the important thing is that it works.

Thanks.

AMAS

 

[shg]

I don't have the slightest clue why it doesn't work from the browser window, ...

Because F2 in the VBE opens the Object Browser.