Putting Array Formula into each Cell in a Selection (that doesn'ttake eons!)
 

I didn't test this for speed, but you can:

Option Explicit
Sub testme01()
Dim sFormula As String
sFormula = "=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC[-5]))"
ActiveCell.FormulaArray = sFormula
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
End Sub


Just a note about Patrick's suggestion.

Try it once more on a test worksheet.
Then Edit|Goto|Special, check current array.

You'll see that that whole range was part of the arrayformula.



CalumMurdo Kennedy wrote:

Thanks Patrick,

That is a whole load faster! However, it has the same problem in that the array doesn't actually adjust accoding to its row. So the array is always the same as the first one, as opposed to be the next row down.

Thanks,

CalumMurdo Kennedy
www.taekwonde.freeserve.co.uk


----- Patrick Molloy wrote: -----

Don't use a for...next loop

sFormula = "=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-
5]),RC[-34]:RC[-5]))"

With Range(Selection, Selection.End(xlDown))
.FormulaArray = sFormula
End With

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,
I'm trying to put an array formula (
=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC
[-5])) ) into
each row of a selection. I've been able to do this with
the
following:
Set MyRows = Range(Selection, Selection.End(xlDown))
i = MyRows.Rows.Count
For j = 2 To i Step 1
Cells(j, 52).FormulaArray =
"=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC
[-5]))"
Next j
However, it takes quite a while once the number of rows
increases
above two hundred or so. Can anyone think of a faster
method as I
expect this to grow to about eight hundred rows or so
and I can't
handle that much caffeine!
Any help is greatly appreciated (as always!)
Best Regards,
CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk
.


--

Dave Peterson

Putting Array Formula into each Cell in a Selection (that doesn't take eons!)
 

I think we have a speed champion! That certainly runs almost
instantaneously over the sheet. One to note me for other things
methinks.

Not only that, I actually understand it too. Thanks for your time
Dave,

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk


Dave Peterson wrote in message ...
I didn't test this for speed, but you can:

Option Explicit
Sub testme01()
Dim sFormula As String
sFormula = "=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC[-5]))"
ActiveCell.FormulaArray = sFormula
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
End Sub


Just a note about Patrick's suggestion.

Try it once more on a test worksheet.
Then Edit|Goto|Special, check current array.

You'll see that that whole range was part of the arrayformula.



CalumMurdo Kennedy wrote:

Thanks Patrick,

That is a whole load faster! However, it has the same problem in that the array doesn't actually adjust accoding to its row. So the array is always the same as the first one, as opposed to be the next row down.

Thanks,

CalumMurdo Kennedy
www.taekwonde.freeserve.co.uk


----- Patrick Molloy wrote: -----

Don't use a for...next loop

sFormula = "=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-
5]),RC[-34]:RC[-5]))"

With Range(Selection, Selection.End(xlDown))
.FormulaArray = sFormula
End With

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,
I'm trying to put an array formula (
=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC
[-5])) ) into
each row of a selection. I've been able to do this with
the
following:
Set MyRows = Range(Selection, Selection.End(xlDown))
i = MyRows.Rows.Count
For j = 2 To i Step 1
Cells(j, 52).FormulaArray =
"=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC
[-5]))"
Next j
However, it takes quite a while once the number of rows
increases
above two hundred or so. Can anyone think of a faster
method as I
expect this to grow to about eight hundred rows or so
and I can't
handle that much caffeine!
Any help is greatly appreciated (as always!)
Best Regards,
CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk
.