Appending Races in an Excel List - Runner names repeating

[Fixador]

Hi All,

Logging to excel, I manually select down the quickpick list, and have the worksheet_change append a list down the Excel Sheet ( i use this sheet as a guide for an overview for the entire day )

But , when say there are 15 runners in the 1450, but only 12 runners in the 1500 race ( next in quickpick list ) - then i get the last 3 runners from the 1450 race on the end of my list for the 1500 !

My code is:-

Code: Select all

'objective : log into BA, manual select each race, export to A1 of this sheet
' as data in - ie, next race copy racename + runner names - appending from line 50 'append each race


Private Sub worksheet_Change(ByVal Target As Range)

Dim RaceDetailsToSave As Integer, EmptyRow As Integer, counter As Integer, Racewatching As String

Application.EnableEvents = False: ThisWorkbook.Worksheets.Application.Calculation = xlCalculationManual

counter = 0
Racewatching = Cells(1, 60)

If Cells(1, 1) <> Racewatching  Then
        Cells(1, 60) = Cells(1, 1)
        'find next empty row in sheets("BA-scalp")
        EmptyRow = Sheets("BA-scalp").Cells(65536, 1).End(xlUp).Row + 2
       

        For RaceDetailsToSave = 1 To 49 Step 1
            If Cells(RaceDetailsToSave, 1) <> "" Then
                Cells(EmptyRow + counter, 1) = Cells(RaceDetailsToSave, 1)
                counter = counter + 1
            Else: Exit For
            End If
        Next RaceDetailsToSave
        Cells(60, 9) = 0: Cells(61, 9) = 0
End If
 
ThisWorkbook.Worksheets.Application.Calculation = xlCalculationAutomatic: ThisWorkbook.Worksheets.Application.EnableEvents = True

End Sub

Do I need to insert a delay or somethingelse before the appending action is performed ??

[thonggruss]

fixador

i always use ("a8:p30").clearcontent if that helps

[Fixador]

Hi thonggruss - i tried that at the end of the code, excel seemed to become unstable , and nothing got appended , and nothing appeared in A8:P30

cheers

I have been trying a PAUSE, but so far it just stops

Code: Select all

Private Sub worksheet_Change(ByVal Target As Range)

Dim RaceDetailsToSave As Integer, EmptyRow As Integer, counter As Integer, Racewatching As String
Dim MyTime1 As Variant, MyTime2 As Variant

Application.EnableEvents = False: ThisWorkbook.Worksheets.Application.Calculation = xlCalculationManual

counter = 0
Racewatching = Cells(1, 60)

'problem: when copies next race, if nos runners less, then copies runners from prior race
'one way out : try a pause before copy ?
If Cells(61, 9) = 1 Then 'occurs on 2nd pass
    Cells(3, 60) = Now
End If
If Cells(61, 9) = 1 Then
    MyTime1 = Cells(2, 60)
    MyTime2 = Cells(3, 60)
End If
If (Cells(60, 9) = "" Or Cells(60, 9) = 0) And Cells(1, 1) <> Racewatching Then  'occurs on 1st pass
     Cells(2, 60) = Now
     Cells(61, 9) = 1
End If



If Cells(1, 1) <> Racewatching And (MyTime2 - MyTime1) >= TimeSerial(0, 0, 3) Then
'If Cells(1, 1) <> Racewatching Then
        Cells(1, 60) = Cells(1, 1)
        'find next empty row in sheets("BA-scalp")
        EmptyRow = Sheets("BA-scalp").Cells(65536, 1).End(xlUp).Row + 2
       

        For RaceDetailsToSave = 1 To 49 Step 1
            If Cells(RaceDetailsToSave, 1) <> "" Then
                Cells(EmptyRow + counter, 1) = Cells(RaceDetailsToSave, 1)
                counter = counter + 1
            Else: Exit For
            End If
        Next RaceDetailsToSave
        Cells(60, 9) = 0: Cells(61, 9) = 0
End If
 
ThisWorkbook.Worksheets.Application.Calculation = xlCalculationAutomatic: ThisWorkbook.Worksheets.Application.EnableEvents = True


End Sub

[throwmeadisc]

I had to use a delay when the next market is loaded of about 3 secs to make sure all the correct info is put out to Excel from BA. Yes the Market will change but I found the runners names are not updated immediately.

Just use a timestamp when the market changes and then set a trigger to copy the info after a pre determined time (3 Secs after timestamp). It takes a little longer to sweep the markets but at least it will be correct. I also use a refresh rate of 0.5 while sweeping info.

Good luck.

Al

[Fixador]

Hi throwmeadisc - thanks for the hint about 3 seconds - got it all working now.

3 secs seems long time ! my trigger finger has fired off to select the next race too soon a few times..... i noticed.........

[throwmeadisc]

I have always used a delay when changing to a new market. I clear all bet refs, triggers and any formula too. Then just re-build it according to how many runners / selections there are. Keeps everything tidy and neat and saves excel calculating formula not being used.

It's a bit of a pain setting it up and converting formula into VBA .FormulaR1C1 etc. but well worth it.

Al