formula for array max if Excel VBA-CodePudding

So this is my formula:

ws8.Range("Y2:Y" & lrowWFS).FormulaArray = "=Max(if(x2='New Stock Data'!H:H,'New Stock Data'!G:G))"

Problem is it's returning to {=Max(if(x2='New Stock Data'!H:H,'New Stock Data'!G:G))} in Y3 onwards.

Any idea how to solve?

CodePudding user response：

It should be as simple as to change FormulaArray to Formula. (I haven't tested this), try:

ws8.Range("Y2:Y" & lrowWFS).Formula = "=Max(if(x2='New Stock Data'!H:H,'New Stock Data'!G:G))"

CodePudding user response：

Couple of things. The generic formula looks like this: {=MAX(IF(criteria_range=criteria,value_range))}. So, instead of x2='New Stock Data'!H:H, you want to use: 'New Stock Data'!H:H=x2. Other than that, unlike regular formulas, array formulas assigned to a range do not adjust relative cell references.

E.g. Range("A1:A2").Formula = "=B1" will successively lead to =B1 and =B2, while Range("A1:A2").FormulaArray = "=B1" will lead to 2x =B1.

One solution is to loop through your range and assign the array formulas to each cell individually. E.g.

Dim rng As Range
Dim lrowWFS As Long

lrowWFS = 3

Set rng = ws8.Range("Y2:Y" & lrowWFS)

For Each cell In rng
    cell.FormulaArray = "=MAX(IF('New Stock Data'!H:H=" & Cells(cell.Row, "X").Address(False, False) & ",'New Stock Data'!G:G))"
Next cell

A better solution would be to use SUMPRODUCT instead. This avoids the use of an array formula, and thus also the need for a loop.

E.g.:

With rng
    .Formula = "=SUMPRODUCT(MAX(('New Stock Data'!H:H=X2)*('New Stock Data'!G:G)))"
End With

More in general, I would advise proper specification of the ranges in the formula. Define first_row and last_row and use something like this:

"=SUMPRODUCT(MAX(('New Stock Data'!H$" & first_row & ":H$" & last_row & "=X2)*('New Stock Data'!G$" & first_row & ":G$" & last_row & ")))"