Substitution for MAXIFS without array formula

1
votes

After a query is completed, I am inserting the following formula into the sheet with the query data using the vba code below. All works great on Excel Office365 but if the version of Excel is 2016 standalone the formula fails with a #NAME error as this function is not available in that version. I have some users that are stuck with it.

I know that a formula array could replace this, but I am not sure how to do this and insert it with code, as well as what the most efficient formula is that could replace this one.

 =IF(OR(ISERROR(MAXIFS(Consumed!D:D,Consumed!B:B,A2)),
MAXIFS(Consumed!D:D,Consumed!B:B,A2)=0),"",
MAXIFS(Consumed!D:D,Consumed!B:B,A2))

Any help appreciated.

strInsertFormula = "=IF(OR(ISERROR(MAXIFS(Consumed!D:D,Consumed!B:B,A2)),MAXIFS(Consumed!D:D,Consumed!B:B,A2)=0),"""",MAXIFS(Consumed!D:D,Consumed!B:B,A2))"

With Sheet3

 .Range("Individual_Bottles").Columns(.Range("EndRng").Offset(0, 1).Column).Insert Shift:=xlToRight
 .Range("EndRng").Offset(-1, 1).Cells(1, 1).Value = "Last Drank"
 .Range("EndRng").Offset(0, 1).Formula = strInsertFormula
 .Range("EndRng").Offset(0, 1).NumberFormat = "yy/mm/dd" 

End With
2
excelvbaexcel-formulaexcel-2016

2 Answers

0
votes

You can use the Excel Array formula =MAX(IF(B:B=A2,D:D,"")) to find the conditional maximum (you can add the extra error checks etc. around this). This will work in all versions from 2016 and earlier.

If you type this into a cell, you do need to press the usual Crtl+Shift+Enter (known as CSE).

If you want to enter this array formula via code, you need to set the FormulaArray property of the range to the above, NOT the Formula property. So your code should read:

.Range("EndRng").Offset(0, 1).FormulaArray = strInsertFormula

where strInsertFormula is the array formula I mentioned above.

0
votes

@JohnF Well I got it working thanks to John F. I had expected that I would have to use a loop but was hoping not to. In the end I did, as I believe the FillDown method requires the range to be selected and I did not want to do that. Here is the final code

        ii = .Range("EndRng").rows.Count
        ConsRows = Consumed.Range("Consumed").rows.Count

        For i = 1 To ii
            strInsertFormula = "=IF(IFERROR(MAX(IF(Consumed!B1:B" & ConsRows & "=" & _
                .Range("A1").Offset(i, 0).Address & _
                ",Consumed!D1:D" & ConsRows & ","""")),0)>0,MAX(IF(Consumed!B1:B" & ConsRows & "=" & _
                .Range("A1").Offset(i, 0).Address & _
                ",Consumed!D1:D" & ConsRows & ","""")),"""")"

            .Range("EndRng").Offset(0, 1).Cells(i, 1).FormulaArray = strInsertFormula
        Next
    End If