Clearing previous market content on auto-bind

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
timevoyager
Posts: 17
Joined: Sun Jul 20, 2014 10:49 pm

I've had this problem in the past too, but for some strange and spooky reason the Status cells began clearing all by themselves eventually... which I believe they are supposed to do automatically with each new event loaded anyway.

If you haven't come across this thread, it may give you some useful ideas...

viewtopic.php?f=31&t=30&start=10
banjocarter
Posts: 12
Joined: Sat Jun 16, 2018 11:22 pm

Hi TimeVoyager

Thanks for your reply. Were you using this with the Betfair version as opposed to the Betdaq version? I'm wondering if at some stage the BF product has had an update applied which wasn't applied to the BD version? Who knows?

Thanks for linking to the other topic, there are a few things in there to try out. The main issue is finding the correct trigger, I'm totally happy with what the resulting code should look like.

Thanks

Ben
timevoyager
Posts: 17
Joined: Sun Jul 20, 2014 10:49 pm

Hi Ben,

I've only dabbled with the Betfair version.
banjocarter
Posts: 12
Joined: Sat Jun 16, 2018 11:22 pm

For information i've now added the following code which will clear the appropriate cells. This is applied to each "Bet Angel" sheet in the workbook individually.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$G$1:$H$1" Then

        If (Range("G1").Value = "In-play") Then
            ' If-- other conditions as appropriate -- Then
        
                Application.EnableEvents = False
                Application.Calculation = xlCalculationManual
                Application.ScreenUpdating = False
    
                ' Clear out cells
                For i = 9 To 67 Step 2
                    Range("O" & i & ":AE" & i).ClearContents
                Next i
        
                For i = 10 To 68 Step 2
                    Range("L" & i & ":AE" & i).ClearContents
                Next i
                
                Application.ScreenUpdating = True
                Application.Calculation = xlCalculationAutomatic
                Application.EnableEvents = True
                
            ' End If -- other conditions as appropriate
            
        End If
       
    End If
    
End Sub
foxwood
Posts: 390
Joined: Mon Jul 23, 2012 2:54 pm

banjocarter wrote:
Sat Oct 13, 2018 11:20 am
For information i've now added the following code which will clear the appropriate cells. This is applied to each "Bet Angel" sheet in the workbook individually.
...
The Target may not explicitly be just those cells - other cells may be included in the range which could make your test fail and not tidy up.

Try this instead which should clear any previous values each time the market name changes (untested btw!) ...

Code: Select all


Dim B1_LastName

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Set B1_LastName = Intersect(Target, Worksheets("Bet Angel").Range("B1"))
    
    If Not B1_LastName Is Nothing Then
    
    	If B1_LastName <> Worksheets("Bet Angel").Range("B1").Value then
    	
    		' new market name found - tidy up from previous market

		' CLEARING CODE HERE
            
        End If
       
    End If
    
End Sub
banjocarter
Posts: 12
Joined: Sat Jun 16, 2018 11:22 pm

Hi Foxwood,

I experimented with using the Intersect function and found the performance to suffer dramatically.

I ran the a quick macro in debug mode and found that Bet angel always seems to update specific cell ranges in sequence. The range G1:H1 is distinctly updated each iteration.

Thanks anyway

Ben
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”