What I need from you is 3 "simple" things to rearrange the code that I have ( or I hope they are simple and you can easily help me to solve this).
I have this sheet that records every odds when the market turns in-play in horse racing and I need:
1- to start recording the market pre-live about 10min before the starting off;
2- when the market turns in-play, separate the 2 range of odds (pre-live and in-play) with a line saying "Live";
3- and the last thing is to copy all odds into a new sheet in the end of the race.
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, 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
I´m sure is very easy to you, but my knowledge of VBA is a little poor. Hope you can help me on this.
Many thanks in advance and thank you for your attention.
Best regards, Eduardo