VBA Unable to set FormulaArray Properties on a formula that is less than 255

[jhedges]

I have a VBA Worksheet_Change event that is looking at a in-cell dropdown. Based upon that case it is calling a macro to insert an array formula that is 163 characters including spaces. I continue to get a run-time error 1004 - Unable to set the FormulaArray Porperty error. Wondering if someone could be of assistance?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B25")) Is Nothing Then
        Select Case Range("B25")
            Case "CARDIO 3 Min. Step Test": InsertArray3min
            Case "CARDIO 1 Mile RockPort": InsertArrayRP
            Case "CARDIO Ebbeling Test": InsertArrayEB
        End Select
    End If
End Sub

These are the macro's called in the above code

Sub InsertArray3min()
    Range("C25").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(PFADATA,MATCH(1,(PFADATA[Member Name]='PFA Report'!$B$4)*(PFADATA[PFA Type]='PFA Report'!$D$12)*(PFADATA[Cardio Test]='PFA Report'!$B$25),0),15),"")"
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
End Sub

Sub InsertArrayRP()
    Range("C25").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(PFADATA,MATCH(1,(PFADATA[Member Name]='PFA Report'!$B$4)*(PFADATA[PFA Type]='PFA Report'!$D$12)*(PFADATA[Cardio Test]='PFA Report'!$B$25),0),20),"")"
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
End Sub

Sub InsertArrayEB()
    Range("C25").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(PFADATA,MATCH(1,(PFADATA[Member Name]='PFA Report'!$B$4)*(PFADATA[PFA Type]='PFA Report'!$D$12)*(PFADATA[Cardio Test]='PFA Report'!$B$25),0),23),"")"
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
End Sub

 

[NARAYANK991]

Hi ,

You need to use four double quotes in your formula , as follows :

[COLOR=#333333]Selection.FormulaArray = _
[/COLOR][COLOR=#333333]        "=IFERROR(INDEX(PFADATA,MATCH(1,(PFADATA[Member Name]='PFA Report'!$B$4)*(PFADATA[PFA Type]='PFA Report'!$D$12)*(PFADATA[Cardio Test]='PFA Report'!$B$25),0),15),"""")"[/COLOR]

Secondly , since you already have a Worksheet_Change procedure in place , it would be better to wrap all lines of code which modify worksheet cells / ranges with the following :

Application.EnableEvents = False

' Your section of code

Application.EnableEvents = True

 

[jhedges]

NARAYANK991,

Thanks for your reply. Sometimes it is the simple things that trip you up. I do have a question - I tried to put the Application.EnableEvents = False and the =True code in the worksheet_change procedure and ran the macro. I also removed it from the change procedure and placed it in the called macro code with the array formulas. Each time when I executed the code the cell where the formula was inserted via my code briefly showed the correct data and then showed the text "Application.EnableEvents=true" in the cell. Any ideas why this is now showing as the cell value, instead of the formula result?

 

[NARAYANK991]

Hi ,

If you can post your code , we should be able to pinpoint the problem ; I tried the following without facing the problem that you mention.

Sub InsertArray3min()
    Application.EnableEvents = False
    Range("C25").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(PFADATA,MATCH(1,(PFADATA[Member Name]='PFA Report'!$B$4)*(PFADATA[PFA Type]='PFA Report'!$D$12)*(PFADATA[Cardio Test]='PFA Report'!$B$25),0),15),"""")"
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Application.EnableEvents = True
End Sub


Sub InsertArrayRP()
    Application.EnableEvents = False
    Range("C25").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(PFADATA,MATCH(1,(PFADATA[Member Name]='PFA Report'!$B$4)*(PFADATA[PFA Type]='PFA Report'!$D$12)*(PFADATA[Cardio Test]='PFA Report'!$B$25),0),20),"""")"
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Application.EnableEvents = True
End Sub


Sub InsertArrayEB()
    Application.EnableEvents = False
    Range("C25").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(PFADATA,MATCH(1,(PFADATA[Member Name]='PFA Report'!$B$4)*(PFADATA[PFA Type]='PFA Report'!$D$12)*(PFADATA[Cardio Test]='PFA Report'!$B$25),0),23),"""")"
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Application.EnableEvents = True
End Sub

 

[jhedges]

I had to move to this for it to work correctly and not insert the text "Application.EnableEvents=true" in the cell.

Sub InsertArray3min()
    ActiveSheet.Unprotect Password:="xx"
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("C25").FormulaArray = "=IFERROR(INDEX(PFADATA,MATCH(1,(PFADATA[Member Name]='PFA Report'!$B$4)*(PFADATA[PFA Type]='PFA Report'!$D$12)*(PFADATA[Cardio Test]='PFA Report'!$B$25),0),15),"""")"
    Range("E25").FormulaArray = "=IFERROR(INDEX(PFADATA,MATCH(1,(PFADATA[Member Name]='PFA Report'!$B$4)*(PFADATA[PFA Type]='PFA Report'!$F$12)*(PFADATA[Cardio Test]='PFA Report'!$B$25),0),15),"""")"
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    ActiveSheet.Protect Password:="xx"
End Sub

 

[Fluff]

@jhedges
Wat was this line of code meant to be doing?

ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True

 

[jhedges]

@jhedges
Wat was this line of code meant to be doing?

ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True

It was from the Macro recorder, when I inserted an array formula.

 

[Fluff]

OK, that makes sense.
It was also the reason that you were getting "Application.EnableEvents=true" pasted into the cells.
As your code was not copying anything, that section of code was pasting whatever was in the clipboard to the file

 

[jhedges]

Thank You for your help. I'm a self-taught application user and have learned a great deal from this site. I often don't know why something works and it is very helpful to understand why.

 

[Fluff]

I'm also self taught & most of what I've learnt has come from here.