Delete market -8 where am I going wrong, deleting two market

[Captain Sensible]

Can anyone give e the run down on how BA executes the -8, -1 triggers as I seem to contiually have problems where the Q2 cell is not being cleared when the market changes and it just deletes or skips the subsequent market. I have some sheets running so I guess I must have figured it out ages ago either by accident or design but wring a new sheet and it's just skips the next market,

Currently using

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
Application.Calculation = xlCalculationManual

Call mydelete_routine
           
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

to trigger off anyone coding. Just seems very hit and miss as someties it won't double delete or skip and sometimes does Is the fact I'm calling a routine causing it to re-enable events and kick off another worksheet change or something. If i log the data it's deleting it seems the old market A1 data is being picked up

[osknows]

I have experienced markets getting stuck after deletion before but it's usually hard to track down as it takes a while to happen. I've used the following before which allows 5 refreshes before re-forcing a market change if it's stuck. The example just continually loops the QPL and deletes any closed or inplay markets

Os

Code: Select all

Option Explicit

Dim currentMarket As String
Dim resetTrigger As Long
Dim nextracetrigger As Long

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngArray() As Variant

    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
   
    With Target.Parent
        rngArray = .Range("A1:BZ55").Value2
       
       If rngArray(1, 1) <> currentMarket Or resetTrigger >= 5 Then
            'New Market Selected
            currentMarket = rngArray(1, 1)
            nextracetrigger = 0
            resetTrigger = 0
            Application.EnableEvents = True
            Exit Sub
        End If
       
        'Count refreshes of next nextracetrigger to a avoid getting stuck.
        If nextracetrigger = 1 Then resetTrigger = resetTrigger + 1
       
        'Delete Closed or Inplay Markets
        If nextracetrigger = 0 And (rngArray(2, 6) = "Closed" Or rngArray(2, 5) = "In Play") Then
            nextracetrigger = 1
            .Range("Q2").Value = -8
        End If
       
        'Cycle Markets
        If nextracetrigger = 0 Then
            If rngArray(3, 10) = "L" Then
                .Range("Q2") = -5
            Else
                .Range("Q2") = -1
            End If
            nextracetrigger = 1
        End If
   
    End With
    Application.EnableEvents = True
   
End Sub

[Captain Sensible]

Thanks Osknows, I've added additional code so it'll only fire now on a specific amount of refreshes and seems OK. Just a pain having it jump or delete two markets, reckon the code must have been retriggering because data wasn't cleared before the next event but hard to track where it was coming from. Just having to set the delete or switch in separate modules now and calling them, seems to work OK.

I'm re-writing some AUS stuff and they takes ages to stick up some markets , certainly past my bedtime, so using the -3 trigger to reload the markets evry two hours then trying to remove unsuitable markets by incorporating the export quickpicklist to excel option to remove markets like the turnover charge ones etc. I used to loop thru them but it takes forever with speed of retreiving AUS and amount of markets. My idea is to reload the quickpick every two hours, clear any unsuitable markets within the next 2.5 hours using delete, then go into bettingmode and repeat.

Be a lot easier if we had triggers to DELETE: a market direct without having to go to it and being able to adjust the auto select market time via excel