Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way to apply a formula
Below is code that copies a formula down a worksheet until it reached the end
of the dataset. The formula is a sumif with variables that move as the formula is copied down. It runs fine, but takes about 2 minutes to complete around 1000 rows of data. I am thinking there has to be a better way to do this. Any suggestions? Dim u1 As Range Dim u2 As Range Dim iSum As Range Dim cSum As Range Dim i As Integer Set u1 = Sheet2.Range("N11") Set u2 = Sheet2.Range("N12") Set iSum = Sheet2.Range("C12:G12") Set cSum = Sheet2.Range("C11:G11") i = 1 Do Until Application.WorksheetFunction.Sum(iSum) = 0 u2 = "=IF(SUM(" & iSum.Address(False, False) & ")=SUM(" & cSum.Address(False, False) & ")," & u1.Address(False, False) & " ," & u1.Address(False, False) & "+" & i & ")" Set u1 = u1.Offset(1, 0) Set u2 = u2.Offset(1, 0) Set iSum = iSum.Offset(1, 0) Set cSum = cSum.Offset(1, 0) Loop Running 2007 with XP SP2. Thanks! PJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way to apply a formula
PJ
A couple of things will speed what you have up massively: Application.screenupdating = false Application.calculation = xlcalculationmanual at the start and reversed at the end will have a big performance impact I normally get the full range first (rather than looping through) either using u1.end xldown or some other logic to find that last cell Then set the formulas in one go: Sheet2.range("N11").select 'needed so formulas are correctly relative Sheet2.range(sheet2.range("N11"), _ sheet2.range("N11").end(xldown)).formula = _ "=IF(SUM(C11:G11)=SUM(C10:G10), N10, N10 + 1)" Maybe this should all be moved down one looking at your code again I'm sure that is slightly wrong somewhere but you should be able to make it do what you want. Cheers Simon Excel development website: www.codematic.net PJFry wrote: Below is code that copies a formula down a worksheet until it reached the end of the dataset. The formula is a sumif with variables that move as the formula is copied down. It runs fine, but takes about 2 minutes to complete around 1000 rows of data. I am thinking there has to be a better way to do this. Any suggestions? Dim u1 As Range Dim u2 As Range Dim iSum As Range Dim cSum As Range Dim i As Integer Set u1 = Sheet2.Range("N11") Set u2 = Sheet2.Range("N12") Set iSum = Sheet2.Range("C12:G12") Set cSum = Sheet2.Range("C11:G11") i = 1 Do Until Application.WorksheetFunction.Sum(iSum) = 0 u2 = "=IF(SUM(" & iSum.Address(False, False) & ")=SUM(" & cSum.Address(False, False) & ")," & u1.Address(False, False) & " ," & u1.Address(False, False) & "+" & i & ")" Set u1 = u1.Offset(1, 0) Set u2 = u2.Offset(1, 0) Set iSum = iSum.Offset(1, 0) Set cSum = cSum.Offset(1, 0) Loop Running 2007 with XP SP2. Thanks! PJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way to apply a formula
Wow, just the
Application.screenupdating = false Application.calculation = xlcalculationmanual took this from a 3 minute macro to a 10 second macro. I am going to try applying the formula to a range next. That never occured to me. Thanks! PJ "Simon Murphy" wrote: PJ A couple of things will speed what you have up massively: Application.screenupdating = false Application.calculation = xlcalculationmanual at the start and reversed at the end will have a big performance impact I normally get the full range first (rather than looping through) either using u1.end xldown or some other logic to find that last cell Then set the formulas in one go: Sheet2.range("N11").select 'needed so formulas are correctly relative Sheet2.range(sheet2.range("N11"), _ sheet2.range("N11").end(xldown)).formula = _ "=IF(SUM(C11:G11)=SUM(C10:G10), N10, N10 + 1)" Maybe this should all be moved down one looking at your code again I'm sure that is slightly wrong somewhere but you should be able to make it do what you want. Cheers Simon Excel development website: www.codematic.net PJFry wrote: Below is code that copies a formula down a worksheet until it reached the end of the dataset. The formula is a sumif with variables that move as the formula is copied down. It runs fine, but takes about 2 minutes to complete around 1000 rows of data. I am thinking there has to be a better way to do this. Any suggestions? Dim u1 As Range Dim u2 As Range Dim iSum As Range Dim cSum As Range Dim i As Integer Set u1 = Sheet2.Range("N11") Set u2 = Sheet2.Range("N12") Set iSum = Sheet2.Range("C12:G12") Set cSum = Sheet2.Range("C11:G11") i = 1 Do Until Application.WorksheetFunction.Sum(iSum) = 0 u2 = "=IF(SUM(" & iSum.Address(False, False) & ")=SUM(" & cSum.Address(False, False) & ")," & u1.Address(False, False) & " ," & u1.Address(False, False) & "+" & i & ")" Set u1 = u1.Offset(1, 0) Set u2 = u2.Offset(1, 0) Set iSum = iSum.Offset(1, 0) Set cSum = cSum.Offset(1, 0) Loop Running 2007 with XP SP2. Thanks! PJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to... apply the same formula to every cell? | Excel Discussion (Misc queries) | |||
automatically apply formula to new row | Excel Programming | |||
Apply Names in Formula | Excel Programming | |||
HOW DO I APPLY A FORMULA FOR AND ENTIRE ROW? | Excel Worksheet Functions | |||
apply formula to other rows ... | Excel Discussion (Misc queries) |