Q2 Trigger

[Shaun]

I have tried a couple codes on here but no luck, i wish to switch markets after bets placed.

I have a formula in T3 that counts the submitted bets and when it equals the amount of bets that are to be placed it puts a 1 in cell Q3

What i need is VBA that will move 1 race forward, then my code will pass the next set of bets then i want the race to move again.

[GaryRussell]

It's cell Q2 that can be used to trigger the next market.
See http://gruss-software.co.uk/forum/viewtopic.php?t=5163 for an example where this is used. Note in this example the use of the flag variable marketSelected to prevent multiple market changes.

[Shaun]

i tried to adapt my code to yours, but not exactly working, does it make any sense.

(code]
Option Explicit

Dim currentMarket As String, marketSelected As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If [A1] <> currentMarket Then marketSelected = False
currentMarket = [A1]
If Sheets("Data").Range("J3").Value = "L" Then
Sheets("Control").Range("P4").Value = "Finished Bets"
GoTo Xit
Else
If Sheets("Data").Range("R3").Value = Sheets("Data").Range("U3").Value And Not marketSelected Then
marketSelected = True
[Q2] = -1
End If
End If
Application.EnableEvents = True
End If
Xit:
End Sub
[/code]

[GaryRussell]

Looks ok. Where does it fail? Do you see -1 appear in Q2?

[Shaun]

No Does not put -1 in the cell

[GaryRussell]

Maybe you should verify that part of your code is running. Use a debug statement. example below. If it does not output "Changing market" in the immediate window then Sheets("Data").Range("U3").Value And Not marketSelected is not evaluating as true.

Code: Select all

Option Explicit

Dim currentMarket As String, marketSelected As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If [A1] <> currentMarket Then marketSelected = False
currentMarket = [A1]
If Sheets("Data").Range("J3").Value = "L" Then
Sheets("Control").Range("P4").Value = "Finished Bets"
GoTo Xit
Else
If Sheets("Data").Range("R3").Value = Sheets("Data").Range("U3").Value And Not marketSelected Then
marketSelected = True
Debug.Print "Changing market"
[Q2] = -1
End If
End If
Application.EnableEvents = True
End If
Xit:
End Sub

[Shaun]

I will have to work on it because it does not fire in the -1