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

[CalumMurdo Kennedy]

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

 

[Frank Kabel]

Hi
try the following:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set MyRows = Range(Selection, Selection.End(xlDown))
i = MyRows.Rows.Count

Range(cells(2,52),cells(i,52))..FormulaArray =
"=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC[-5]))"
Next j

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[Guest]

Thanks Frank

Thats much quicker (once I added the For , the only problem is that the array formula does not change according to its position, so each one refers to the first row, is this a result of the manual calculation? Would it be an option to put the j value into the formula (I tried it but got an error)

Thanks again
Best Regards

CalumMurdo Kenned
www.taekwondo.freeserve.co.u


----- Frank Kabel wrote: ----

H
try the following

Application.ScreenUpdating = Fals
Application.Calculation = xlCalculationManua
Set MyRows = Range(Selection, Selection.End(xlDown)
i = MyRows.Rows.Coun

Range(cells(2,52),cells(i,52))..FormulaArray
"=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC[-5]))
Next

Application.ScreenUpdating = Tru
Application.Calculation = xlCalculationAutomati



-
Regard
Frank Kabe
Frankfurt, German


CalumMurdo Kennedy wrote

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])) ) int
each row of a selection. I've been able to do this with th
following

Set MyRows = Range(Selection, Selection.End(xlDown)

i = MyRows.Rows.Coun
For j = 2 To i Step
Cells(j, 52).FormulaArray
"=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC[-5]))
Next

However, it takes quite a while once the number of rows increase

above two hundred or so. Can anyone think of a faster method as
expect this to grow to about eight hundred rows or so and I can'
handle that much caffeine

Any help is greatly appreciated (as always!
Best Regards
CalumMurdo Kenned

www.taekwondo.freeserve.co.u

 

[Patrick Molloy]

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

 

[Guest]

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 Kenned
www.taekwonde.freeserve.co.u


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

Don't use a for...next loo

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

With Range(Selection, Selection.End(xlDown)
.FormulaArray = sFormul
End Wit

Patrick Mollo
Microsoft Excel MV

-----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]:R [-5])) ) int
each row of a selection. I've been able to do this with th

following
Set MyRows = Range(Selection, Selection.End(xlDown)

i = MyRows.Rows.Coun
For j = 2 To i Step
Cells(j, 52).FormulaArray
"=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:R [-5]))

Next
However, it takes quite a while once the number of rows

increase
above two hundred or so. Can anyone think of a faster method as
expect this to grow to about eight hundred rows or so and I can'
handle that much caffeine

Any help is greatly appreciated (as always!
Best Regards
CalumMurdo Kenned

www.taekwondo.freeserve.co.u

 

[Frank Kabel]

Hi
sorry should have tested this. Try

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set MyRows = Range(Selection, Selection.End(xlDown))
i = MyRows.Rows.Count
for j = 2 to i
cells(j,52).FormulaArray =
"=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC[-5]))"
Next j

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


--
Regards
Frank Kabel
Frankfurt, Germany

Thanks Frank,

Thats much quicker (once I added the For , the only problem is
that the array formula does not change according to its position, so
each one refers to the first row, is this a result of the manual
calculation? Would it be an option to put the j value into the
formula (I tried it but got an error)?

Thanks again,
Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk


----- Frank Kabel wrote: -----

Hi
try the following:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set MyRows = Range(Selection, Selection.End(xlDown))
i = MyRows.Rows.Count

Range(cells(2,52),cells(i,52))..FormulaArray =
"=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC[-5]))"
Next j

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic



--
Regards
Frank Kabel
Frankfurt, Germany

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!

www.taekwondo.freeserve.co.uk

 

[Frank Kabel]

Hi Patrick
that was also my idea but the row index would not change. So you'll
have to use a for next loop

--
Regards
Frank Kabel
Frankfurt, Germany

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
.

 

[Guest]

Thanks Frank

The world is now at peace, thanks very much

Best Regards

CalumMurdo Kenned
----- Frank Kabel wrote: ----

H
sorry should have tested this. Tr

Application.ScreenUpdating = Fals
Application.Calculation = xlCalculationManua
Set MyRows = Range(Selection, Selection.End(xlDown)
i = MyRows.Rows.Coun
for j = 2 to
cells(j,52).FormulaArray
"=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC[-5]))
Next

Application.ScreenUpdating = Tru
Application.Calculation = xlCalculationAutomati


-
Regard
Frank Kabe
Frankfurt, German


CalumMurdo Kennedy wrote

Thanks Frank

Thats much quicker (once I added the For , the only problem i

that the array formula does not change according to its position, s
each one refers to the first row, is this a result of the manua
calculation? Would it be an option to put the j value into th
formula (I tried it but got an error)

Thanks again Best Regards
CalumMurdo Kenned www.taekwondo.freeserve.co.u
H try the following
Application.ScreenUpdating = Fals

Application.Calculation = xlCalculationManua
Set MyRows = Range(Selection, Selection.End(xlDown)
i = MyRows.Rows.Coun

Range(cells(2,52),cells(i,52))..FormulaArray "=MIN(IF(RC[-34]:RC[-5]<=1,MAX(RC[-34]:RC[-5]),RC[-34]:RC[-5]))
Next
Application.ScreenUpdating = Tru

Application.Calculation = xlCalculationAutomatiRegard
Frank Kabe
Frankfurt, German

CalumMurdo Kennedy wrote 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 thi
with the > following

Set MyRows = Range(Selection, Selection.End(xlDown)

i = MyRows.Rows.Coun
For j = 2 To i Step
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 numbe

of rows increases > above two hundred or so. Can anyone thin
of a faster method as I > expect this to grow to about eigh
hundred rows or so and I can't > handle that much caffeine

www.taekwondo.freeserve.co.u

 

[Dave Peterson]

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.

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
.

 

[CalumMurdo Kennedy]

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

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.

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
.