Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I assign a value to a cell without putting a formula in it | Excel Worksheet Functions | |||
Any Way to Save Eons of Typing? Automating 3D Reference to Sheet N | Excel Worksheet Functions | |||
Array with multiple selection criteria | Excel Worksheet Functions | |||
Problem with Vlookup array selection | Excel Worksheet Functions | |||
putting a string from one cell in the formula of another -- indirect needed? | Excel Worksheet Functions |