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
Clearing previous market content on auto-bind
-
- 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
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
-
- Posts: 17
- Joined: Sun Jul 20, 2014 10:49 pm
Hi Ben,
I've only dabbled with the Betfair version.
I've only dabbled with the Betfair version.
-
- 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
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.banjocarter wrote: ↑Sat Oct 13, 2018 11:20 amFor 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.
...
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
-
- 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
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