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