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