Error with array formula in VBA

Question

 A      B    C   D  
east    1   56
west    5   98
east    1   78
west    5   99
south   3   23
east    2   45
south   3   67

I want to get the maximum value of same combination of A+B concatenated. Ex- for east1, I should get 78 in column D. For this I am using VBA, but the code doesn't seem to work. I am using array formula for it.

My code is:

.Range("D2:D" & OutputLastRow).FormulaArray = "=MAX(("$A$2:$A$" & OutputLastRow=A2)*("$B$2:$B$" & OutputLastRow=B2)*("$C$2:$C$" & OutputLastRow))"

The code is running without any bug but the result comes out to be wrong because the cell A2 as well as B2 is not getting updated as the formula moves down from D2. A2 remains as A2 and B2 as B2. I have tried it using loop but that also doesn't work.

That line can't run without a bug - it is syntactically incorrect. — YowE3K
– YowE3K, Commented Jul 22, 2017 at 22:24
Consider creating a Pivot Table. It might result in a neater and more readable report. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 23, 2017 at 19:19

A.S.H · Accepted Answer · 2017-07-22 22:32:27Z

4

When using .FormulaArray on a multi-cell range, it's not like putting an array formula in the first cell and auto-filling. You should rather do it explicitly, in two steps. Your code, after some corrections, should be like this:

' 1- Enter the array formula in top cell
Range("D2").FormulaArray = _
  "=MAX(($A$2:$A$" & OutputLastRow & "=A2)*($B$2:$B$" & OutputLastRow & _
  "=B2)*($C$2:$C$" & OutputLastRow & "))"

' 2- Then autofill down the column
Range("D2:D" & OutputLastRow).FillDown

Alternatively, you could use the AGGREGATE function to make a "normal" formula which is exactly equivalent to yours:

Range("D2:D" & OutputLastRow).Formula = _
  "=Aggregate(14, 6, ($A$2:$A$" & OutputLastRow & "=A2)*($B$2:$B$" & OutputLastRow & _
  "=B2)*($C$2:$C$" & OutputLastRow & "), 1)"

edited Jul 22, 2017 at 22:32

answered Jul 22, 2017 at 22:25

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

A.S.H Over a year ago

@AndreTerra Aggregate was introduced with Excel 2010. It's really powerful and cool. Nevertheless it took a while before I started using it and I often meet Excel skillful users who didn't add it to their arsenal yet.

Shashank Over a year ago

@A.S.H..Thanks..The second one with aggregate function worked for me but the array one is still having the same issue of non-updating the cells A2 and B2. I still can't figure out why is it happening...

A.S.H Over a year ago

@ShashankChandra it could be due to some errors or #NAs in the range. The AGGREGATE function with option 6 permits to ignore those errors.

airstrike · Accepted Answer · 2017-07-22 22:16:45Z

1

You don't really need VBA for this. In D1 enter the following array formula (using Ctrl+Shift+Enter instead of just Enter) and drag it down

=MAX(IF(A1&B1=$A$1:$A$7&$B$1:$B$7,$C$1:$C$7))

Your output should look like this:

The formula works because MAX and MIN ignore the FALSE values for A1&B1 = $A$1:$A$7&$B$1:$B$7 and operate only on the remaining results. The array formula makes it so that the A1&B1 gets compared to the "current row" value for $A$1:$A$7&$B$1:$B$7. I guess you could lock columns for $A1&$B1 like that but in your example it wasn't necessary.

answered Jul 22, 2017 at 22:16

airstrike

2,4211 gold badge27 silver badges28 bronze badges

Collectives™ on Stack Overflow

Error with array formula in VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related