excel vba FormulaArray

0
votes

I'm trying to build an advanced filter table in excel vba like in the picture below. In the last column I'm using the below array formula.

Worksheets("Contracts").Range("N13:N" & Worksheets("contracts").Range("M" & Rows.Count).End(xlUp).Row).FormulaArray = _
"=sum(if(invoice!$A$3:$A$2000=contracts!B13,invoice!$AA$3:$AL$2000))"

The issue I'm having is that it copies the formula to all cells in range without changing the cell from: contracts!B13. It stays the same. B13.

Advanced Filter

Where I'm wrong?

Thank you, Daniel

1
excelvbaformula
Well, what should it change to? C13, D13.. and so on? - Tom K.
It should change to B14, B15, B16, and so on. :-) - Ch Daniel

1 Answers

0
votes

You can do it like this:

lastRow = Range("M" & Rows.Count).End(xlUp).Row
For i = 13 to lastRow
    Worksheets("Contracts").Range("N13:N" & i).Formula = _
    "=sum(if(invoice!$A$3:$A$2000=contracts!B" & i & ",invoice!$AA$3:$AL$2000))"
Next