I need 2 "simple" things to rearrange the code that I have ( or I hope they are simple and you can easily help me to solve this). The code records already every odds 10 min. before off, but I need:
1- Differentiate the pre-live and live odds. I´m thinking to put an "x" at the final of each raw on the pre-live odds, that would be in the column CG.
2- At the end of the race, copy all odds and save them in a new workbook with the name of the market (ex. Fairy 11th Jan - 16:00 2m INHF).
This is the code that I have now:
- Code: Select all
Option Explicit
Dim currentMarket As String
Dim lastOddsUpdate As Date
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngArray() As Variant
Dim oddsArray(1 To 1, 5 To 54) As Variant
Dim i As Long
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With Target.Parent
rngArray = .Range("A1:AZ55").Value2
If rngArray(1, 1) <> currentMarket Then
'New Market Selected
currentMarket = rngArray(1, 1)
.Range("AI5:CF1048576").ClearContents
End If
If rngArray(2, 23) = "1" Or rngArray(2, 5) = "In Play" And rngArray(2, 6) <> "Suspended" Then
If DateDiff("s", lastOddsUpdate, rngArray(2, 2)) >= (rngArray(1, 36) * 86400) Then
lastOddsUpdate = rngArray(2, 2)
For i = 5 To 50
If rngArray(i, 1) = "" Then Exit For
oddsArray(1, i) = rngArray(i, 15)
Next i
.Range("AI1:CF1").Offset(rngArray(3, 35) + 4, 0).Value = oddsArray
End If
End If
End With
Application.EnableEvents = True
End Sub
My knowledge of VBA is very poor. Hope you can help me on this.
Many thanks in advance and thank you for your attention.
Best regards, Eduardo
