arrays - two - Error con la fórmula de matriz en VBA

visual basic char array

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

Quiero obtener el valor máximo de la misma combinación de A + B concatenado. Por ejemplo, para east1, debería obtener 78 en la columna D. Para esto estoy usando VBA, pero el código no parece funcionar. Estoy usando una fórmula de matriz para eso.

Mi código es:

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

El código se está ejecutando sin ningún error, pero el resultado es incorrecto porque la celda A2 y B2 no se actualizan a medida que la fórmula desciende desde D2. A2 permanece como A2 y B2 como B2. Lo he intentado usando loop pero eso tampoco funciona.

Answer 1

Al usar .FormulaArray en un rango de varias celdas, no es como poner una fórmula de matriz en la primera celda y llenar automáticamente. Deberías hacerlo explícitamente, en dos pasos. Su código, después de algunas correcciones, debería ser así:

'' 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 

Alternativamente, puede usar la función AGGREGATE para hacer una fórmula "normal" que es exactamente equivalente a la suya:

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

Answer 2

Realmente no necesitas VBA para esto. En D1 ingrese la siguiente fórmula de matriz (usando Ctrl + Mayús + Entrar en lugar de simplemente Enter) y arrástrela hacia abajo

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

Tu salida debería verse así:

La fórmula funciona porque MAX y MIN ignoran los valores FALSOS para A1&B1 = $A$1:$A$7&$B$1:$B$7 y operan solo en los resultados restantes. La fórmula de matriz hace que el A1&B1 se compare con el valor de "fila actual" por $A$1:$A$7&$B$1:$B$7 . Supongo que podría bloquear las columnas para $A1&$B1 así, pero en su ejemplo no era necesario.