Help with formula

[MikeSea45]

I'm trying to use an array formula as shown below, but it does not work. I think it's something to do with " .FormulaR1C1.Array" part of it, can anyone advise what I've done wrong?



With Range("AA3:AA" & LastRow)
.FormulaR1C1.Array = "=SUMPRODUCT(--(Weeks!R3C1:R260000C1=RC2),--(Weeks!R3C4:R260000C4=RC8),Weeks!R3C5:R260000C5)"
.Value = .Value
End With

 

[Andrew Poulsom]

It's just FormulaArray.

 

[MikeSea45]

Thanks, I've just spotted that when the formula is entered it is looking in the same row (3) for the match value. Is there a way in which the lookup row looks at the current row for the match?

 

[Andrew Poulsom]

I don't understand. Which part of the formula do you want to change? The row in RC2 and RC8 will adjust relatively for each row.