Help with multiple trigger PLEASE!

[Conrad1971]

Hi,
I am using a three stage trigger at the moment which seems to work fine 90% of the time, but occasionally the second part of my trigger triggers multiple times (if the odds on that particular horses go up and down very quickly a number of times). Is there a simple comand i can add after the second trigger has gone the first time to stop it repeating?
Any help would be greatly appreciated,
Conrad.

[osknows]

Hello,

You are probably better off moving your conditions to VBA so you can track when triggers have fired. Eg the below example shows a very simple formula in cell AA5

[AA5]=IF(E6>5,TRUE,FALSE)

The code checks for when AA5 is true then overwrites the formula as a value locking it. When the market changes then the formula is re-applied.

This is just one of many ways to achieve similar. It may be easier to do away with cell formula and do it all with VBA

Code: Select all

Option Explicit

Dim currentMarket As String
Dim trigger1 As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then
    Application.EnableEvents = False

    With Target.Parent

        If .Range("A1").Value <> currentMarket Then
            currentMarket = .Range("A1").Value
            trigger1 = False
            'reapply formula
            .Range("AA5").Formula = "=IF(E6>5,TRUE,FALSE)"
        End If
       
        'fire only if trigger not fired/False
        If Not trigger1 And .Range("AA5").Value = True Then
            trigger1 = True
            'lock formula as value
            .Range("AA5").Value = .Range("AA5").Value
        End If
    End With

   Application.EnableEvents = True
End If

End Sub

[Conrad1971]

Thanks osknows

Code: Select all

What i am trying to do i dont think is that complicated, all i want to do is  back a horse when it reaches certain odds (lets say 10), then if the same horses odds go down (lets say to 8) then i want to lay it, then if the same horses odds go back up (lets say to 14) i want to back it again. My trigger for the above senario would be:-

=IF(AND(X6=0,F6=10),"BACK",IF(AND(X6>1,F6=8),"LAY",IF(AND(X6=10,F6=14),"BACK","CLEAR")))

What is happening is the second part of the bet (the lay bet @8) is trigering multiple times, i.e if the odds go up to 8 then down to 7, then up to 8, down to 7........., then i end up with up to 5 lay bets and a huge loss on that horse.

I hope you can understand my explanation
Is there just a simple fix for this, like adding something after the second trigger formula so that once it has triggered it places the second bet then waits for the 3rd trigger criteria to be matched?

Thanks for all your help,
Conrad.

[Conrad1971]

Sorry osknows, everywhere i typed the number eight, the number seems to have been replaced with a smily face and glasses!!!!

[osknows]

If I've understood correctly you want to BACK, then LAY, then BACK again in an ordered cycle based on your criteria. One way is to use column Y as an indication of where you are in the cycle.

The formula in [Q5] would update to i

Code: Select all

=IF(AND(X5=0,F5=10,Y5=""),"BACK",IF(AND(X5>1,F5=8,Y5="BACK1"),"LAY",IF(AND(X5=10,F5=14,Y5="LAY1"),"BACK",IF(T5<>"","CLEAR",""))))

And this code will track the order of the cycle and write to column Y

This goes in the sheet object

Code: Select all

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

If Target.Columns.Count = 16 Then
    Application.EnableEvents = False

    With Target.Parent
   
        If .Range("A1").Value <> currentMarket Then
            currentMarket = .Range("A1").Value
            .Range("Y5:Y55").ClearContents
        End If
   
        For i = 5 To 55
       
            If .Range("Q" & i).Value = "BACK" And .Range("Y" & i).Value = "" Then
                .Range("Y" & i).Value = "BACK1"
            ElseIf .Range("Q" & i).Value = "LAY" Then
                .Range("Y" & i).Value = "LAY1"
            ElseIf .Range("Q" & i).Value = "BACK" And .Range("Y" & i).Value = "LAY1" Then
                .Range("Y" & i).Value = "BACK2"
            ElseIf .Range("Y" & i).Value = "BACK2" Then
                .Range("Y" & i).ClearContents
            End If
               
        Next i

    End With

   Application.EnableEvents = True
End If

End Sub

One other thing to consider, you are checking for odds matching exact odds, eg F5 = 10, F5 = 8, F5 = 14 what about if they are 10.2, 8.4 & 13.8?

[2020vision]

Sorry osknows, everywhere i typed the number eight, the number seems to have been replaced with a smily face and glasses!!!!

Hi Conrad

Hope you don't mind but I have edited your post above just slightly to get
rid of the smiley faces. When you are posting formulas and suchlike then you
need to use the "code" and then" /code" buttons above the comment window
around any Excel code, this will avoid the smileys and make it readable to all.

Regards - Michael

[Conrad1971]

Thanks very much osknows for all your help, it is much appreciated.
Thanks Michael for editting my post, i will know about the code box for next time.