Can anyone recode this macro to run effeciently/faster?

[tiger12345]

Hi all,
Just wanted to know how to make this macro run effeciently/faster?
I have only put 4 sets of calculations in here, but there are actually
86 more sets of calculations in the macro.
Any help or suggestions would be very much appreciated.
Regareds.


Private Sub Worksheet_Calculate()

Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim m As Long
Dim sRow As Long
Dim eRow As Long
Dim cCol As Long
Dim rCol As Long
Dim oCol As Long
Dim sCol As Long
sRow = 27 'Start row
eRow = 277 'End row
cCol = 19 'Check fine column
rCol = 20 'Results trigger column
oCol = 21 'Choice column
sCol = 22 'Fine column

With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For i = sRow To eRow
If Cells(i, cCol) < 11 And Cells(i, cCol) > 1 Then
For j = sRow To eRow
If Cells(j, cCol) > 10 And Cells(j, cCol) < 10000 Then
Cells(i, rCol) = "FINE"
Cells(i, oCol) = "11"
Cells(i, sCol) = "30000"

Cells(j, rCol) = "FINE"
Cells(j, oCol) = "10000"
Cells(j, sCol) = "20"
End If
Next j
End If
Next i
For i = sRow To eRow
If Cells(i, cCol) < 106 And Cells(i, cCol) > 1 Then
For j = sRow To eRow
If Cells(j, cCol) > 105 And Cells(j, cCol) < 176 Then
For k = sRow To eRow
If Cells(k, cCol) > 175 And Cells(k, cCol) < 263 Then
Cells(i, rCol) = "FINE"
Cells(i, oCol) = "105"
Cells(i, sCol) = "23000"

Cells(j, rCol) = "FINE"
Cells(j, oCol) = "175"
Cells(j, sCol) = "25"
Cells(k, rCol) = "FINE"
Cells(k, oCol) = "262"
Cells(k, sCol) = "10"
End If
Next k
End If
Next j
End If
Next i
For i = sRow To eRow
If Cells(i, cCol) < 187 And Cells(i, cCol) > 1 Then
For j = sRow To eRow
If Cells(j, cCol) > 186 And Cells(j, cCol) < 233 Then
For k = sRow To eRow
If Cells(k, cCol) > 232 And Cells(k, cCol) < 311 Then
For l = sRow To eRow
If Cells(l, cCol) > 310 And Cells(l, cCol) < 471 Then
Cells(i, rCol) = "FINE"
Cells(i, oCol) = "186"
Cells(i, sCol) = "4000"

Cells(j, rCol) = "FINE"
Cells(j, oCol) = "232"
Cells(j, sCol) = "500"
Cells(k, rCol) = "FINE"
Cells(k, oCol) = "310"
Cells(k, sCol) = "8000"

Cells(l, rCol) = "FINE"
Cells(l, oCol) = "470"
Cells(l, sCol) = "1000"
End If
Next l
End If
Next k
End If
Next j
End If
Next i
For i = sRow To eRow
If Cells(i, cCol) < 331 And Cells(i, cCol) > 1 Then
For j = sRow To eRow
If Cells(j, cCol) > 330 And Cells(j, cCol) < 396 Then
For k = sRow To eRow
If Cells(k, cCol) > 395 And Cells(k, cCol) < 491 Then
For l = sRow To eRow
If Cells(l, cCol) > 490 And Cells(l, cCol) < 661 Then
For m = sRow To eRow
If Cells(m, cCol) > 660 And Cells(m, cCol) < 981 Then
Cells(i, rCol) = "FINE"
Cells(i, oCol) = "330"
Cells(i, sCol) = "3000"

Cells(j, rCol) = "FINE"
Cells(j, oCol) = "395"
Cells(j, sCol) = "2500"
Cells(k, rCol) = "FINE"
Cells(k, oCol) = "490"
Cells(k, sCol) = "2000"

Cells(l, rCol) = "FINE"
Cells(l, oCol) = "660"
Cells(l, sCol) = "1005"

Cells(m, rCol) = "FINE"
Cells(m, oCol) = "980"
Cells(m, sCol) = "1000"
End If
Next m
End If
Next l
End If
Next k
End If
Next j
End If
Next i

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[brettdj]

Rather than use ranges

1) Dump your ranges to a variant array
2) Manipulate the data in the array
3) Dump the variant array back to the ranges at the end

I'm short of time right now, but these two examples of mine should help on the suggested process
1) Using Variant Arrays in Excel VBA for Large Scale Data Manipulation http://www.experts-exchange.com/A_2684.html
2) Creating and Writing to a CSV File Using Excel VBA Article http://www.experts-exchange.com/A_3509.html

Regards

Dave

 

[shg]

A place to start would be to take it out of the Calculate event.

It looks like the code could be replaced with simple formulas on the worksheet.