Autofill an Array with a Macro

[bluedevil]

Hi,

I have an array (below) formula that I need to enter into a list of data using a macro.

=MAX(IF($B$2:$B$194=B2,$N$2:$N$194))

The bolded B2 needs to change to match the row that it is entered in.

The forumla I have used to enter the array is:

Range("O2:O" & LR).FormulaArray = "=MAX(IF(R2C2:R" & LR & "C2=RC[-13],R2C14:R" & LR & "C14))"

(LR is a count of the last row with data).

The problem I have is that when I do this the B2 doesn't update as it would when you Autofill. I have recorded the code for Autofill and tried using that, but I get an error message.

At the moment I have a Loop in place that does what I need, however due to the large volumes of data this takes a lot of time. For reference my copde for the loop is:
Do
Row = ActiveCell.Row
ActiveCell.FormulaArray = "=MAX(IF(R2C2:R" & LR & "C2=RC[-13],R2C14:R" & LR & "C14))"
ActiveCell.Offset(1, 0).Select
Loop Until Row = LR


Is there any way I can sort this without using the loop?

Thank you for any help you can give,
Paul

 

[Crystalyzer]

I got this code to work using autofill so you would not need to incorporate this into a loop.

Range("O2").FormulaArray = "=MAX(IF(R2C1:R26C1=RC[-14],R2C2:R26C2))"
Range("O2").AutoFill Destination:=Range("O2:O" & LR)

Notice that I am placing the array formula in column "O" and that in the array formula I am using [-14] instead of the [-13] that you had since my customer is in column "A".

Let me know how this works out for you.

 

[bluedevil]

That is brilliant, not sure what I was doing with the autofill code but that has worked a charm!
Thanks very much.