Slow Calculations

[mikey]

I have a sheet which is calculating the lowest,highest and starting odds ect for all horses Inplay, This works really well but it takes a few seconds to run through all of the horses, Is there a way of speeding this up?



code :

Private Sub Worksheet_Change(ByVal Target As Range)



' Only process whole updates
If Target.Columns.Count <> 16 Then Exit Sub


Application.EnableEvents = False

For Each cell In Target
iRow = cell.Row
iCol = cell.Column


' Update Max and Min Back Values
If iCol = 15 And iRow > 4 And iRow < 46 Then

Application.EnableEvents = False
If IsEmpty(Range("CJ" & iRow).Value) Then Range("CJ" & iRow).Value = 0
If IsEmpty(Range("CI" & iRow).Value) Then Range("CI" & iRow).Value = 1001
If IsEmpty(Range("CH" & iRow).Value) Then Range("CH" & iRow).Value = Range("O" & iRow).Value

If Not IsEmpty(cell.Value) Then
Range("CG" & iRow).Value = Range("O" & iRow).Value
If cell.Value < Range("CI" & iRow).Value And cell.Value > 1 Then Range("CI" & iRow).Value = cell.Value
If cell.Value > Range("CJ" & iRow).Value And cell.Value < 1001 Then Range("CJ" & iRow).Value = cell.Value
End If

Application.EnableEvents = True
End If



Next cell

[osknows]

The best way is to write the data to arrays, do the calcs in an array then write back the whole array once only.

The code below should be close but is untested as I've made this up on the fly at work without BA or the excel spreadsheets in front of me but you should see how it works

There are 2 arrays
BA_Changes captures the changes made by BA
strArray1 is the range cg5:cj55

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

' Only process whole updates
If Target.Columns.Count <> 16 Then Exit Sub



BA_Changes = Target
Set rng = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("cg5:cj55")
strArray1 = rng


For i = 1 To UBound(BA_Changes)


If IsEmpty(strArray1(i, 4)) Then strArray1(i, 4) = 0  'i is row number, 4 is column CJ, 3 is CI, 2 is CH and 1 is CG
If IsEmpty(strArray1(i, 3)) Then strArray1(i, 3) = 1001
If IsEmpty(strArray1(i, 2)) Then strArray1(i, 2) = BA_Changes(i, 15)

If Not IsEmpty(BA_Changes(i, 15)) Then
    strArray1(i, 1) = BA_Changes(i, 15)

    If BA_Changes(i, 15) < strArray1(i, 3) And BA_Changes(i, 15) > 1 Then strArray1(i, 3) = BA_Changes(i, 15)
    If BA_Changes(i, 15) > strArray1(i, 4) And BA_Changes(i, 15) < 1001 Then strArray1(i, 4) = BA_Changes(i, 15)

End If

Next i

ThisWorkbook.Sheets(Target.Worksheet.Name).Range("cg5:cj55").Value = strArray1

Application.EnableEvents = True
End Sub


What I can't remememebr is if the BA update starts from row 1 or row 5? If the values come out wrong change cg5:cj55 to cg1:cj55

[mikey]

Thanks Osknows, I think its row 5, I'll give it try later.

[mikey]

Osknows, works really well , had to adjust a couple of things , really appreciate your help. Thanks.