Cumbersome Code

[Jim Berglund]

The following code takes a R E A L L Y long time (90 seconds), processing around 2000 records. Is there any way to speed it up?

.Columns("Y").NumberFormat = "0"
For i = 3 To (numberofRows - 2)
If .Range("CE5") = "DIST" Then
If .Cells(5, 86).Value - .Cells(i, 25).Value > 0 Then
.Cells(i, 57).Value = .Cells(5, 86).Value - .Cells(i, 25).Value
Else
End If
.Cells(i, 57).Value = 0
End If
Next

Thanks,
Jim Berglund

 

[David McRitchie]

Hi Jim,
Don't know what you have for numberofrows value,
there is no need to do any checking past the used range.

To speed things up whether good or bad code, turn off screen
updating and calculation during the running of the macro.

http://www.mvps.org/dmcritchie/excel/slowresp.htm


The following code takes a R E A L L Y long time (90 seconds), processing around 2000 records. Is there any way to speed it up?

.Columns("Y").NumberFormat = "0"
For i = 3 To (numberofRows - 2)
If .Range("CE5") = "DIST" Then
If .Cells(5, 86).Value - .Cells(i, 25).Value > 0 Then
.Cells(i, 57).Value = .Cells(5, 86).Value - .Cells(i, 25).Value
Else
End If
.Cells(i, 57).Value = 0
End If
Next

Thanks,
Jim Berglund

 

[Rob van Gelder]

Guess: Pagebreak preview sometimes slows things down.
Try: Worksheets("Sheet1").DisplayPageBreaks = False

You could also copy .Cells(5, 86).Value into a variable so that it doesn't need to be read every time.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


The following code takes a R E A L L Y long time (90 seconds), processing around 2000 records. Is there any way to speed it up?

.Columns("Y").NumberFormat = "0"
For i = 3 To (numberofRows - 2)
If .Range("CE5") = "DIST" Then
If .Cells(5, 86).Value - .Cells(i, 25).Value > 0 Then
.Cells(i, 57).Value = .Cells(5, 86).Value - .Cells(i, 25).Value
Else
End If
.Cells(i, 57).Value = 0
End If
Next

Thanks,
Jim Berglund

 

[Dana DeLouis]

Just an observation:
Note that:
If .Range("CE5") = "DIST" Then...
It looks like this never changes, so generally, it should not be inside your
loop. Looks like some code logic that could be improved here.

Don't know if this is any faster, but here is just a general idea...

Dim R As Long 'R for Row
Dim N As Double

N = Cells(5, 86).Value

For R = 3 To (numberofRows - 2)
Cells(R, 57).Value = WorksheetFunction.Max(0, N - Cells(R, 25))
Next R

HTH
--
Dana DeLouis
Win XP & Office 2003


The following code takes a R E A L L Y long time (90 seconds), processing
around 2000 records. Is there any way to speed it up?

.Columns("Y").NumberFormat = "0"
For i = 3 To (numberofRows - 2)
If .Range("CE5") = "DIST" Then
If .Cells(5, 86).Value - .Cells(i, 25).Value > 0 Then
.Cells(i, 57).Value = .Cells(5, 86).Value - .Cells(i, 25).Value
Else
End If
.Cells(i, 57).Value = 0
End If
Next

Thanks,
Jim Berglund

 

[RB Smissaert]

2 things to speed this up.

Firstly, this check:
If .Range("CE5") = "DIST" Then
should be out of the loop, because the result doesn't change in the loop.
It means it has to be checked only once and not 2000 times.
The same applies to this value
..Cells(5, 86).Value
No need to get this value 2000 times.
Seconly, I would put the ranges in arrays, do the checking and altering in
these arrays
and then write the altered array back to the sheet. Array manipulations are
always much
faster then range manipulations.

With these 2 things your code would be something like this:

Sub test()

Dim i As Long
Dim LR As Long
Dim arr1()
Dim arr2()
Dim strMainCheck As String
Dim dNumberCheck As Double

LR = 2000

With Sheets(1)

.Columns("Y").NumberFormat = "0"
strMainCheck = .Cells(5, 83).Value
dNumberCheck = .Cells(5, 86).Value
arr1 = Range(.Cells(3, 25), .Cells(LR, 25))
arr2 = Range(.Cells(3, 57), .Cells(LR, 57))

If strMainCheck = "DIST" Then
For i = 1 To LR - 2
If dNumberCheck - arr1(i, 1) > 0 Then
arr2(i, 1) = dNumberCheck - arr1(i, 1)
Else
arr2(i, 1) = 0
End If
Next
Range(.Cells(3, 57), .Cells(LR, 57)) = arr2
End If

End With

End Sub

You will have to work it out a bit further, but this should make it much
faster.



RBS



The following code takes a R E A L L Y long time (90 seconds), processing
around 2000 records. Is there any way to speed it up?

.Columns("Y").NumberFormat = "0"
For i = 3 To (numberofRows - 2)
If .Range("CE5") = "DIST" Then
If .Cells(5, 86).Value - .Cells(i, 25).Value > 0 Then
.Cells(i, 57).Value = .Cells(5, 86).Value - .Cells(i, 25).Value
Else
End If
.Cells(i, 57).Value = 0
End If
Next

Thanks,
Jim Berglund