Trying to use the Q2 -1 trigger

[ians1711]

Hi
Trying to use Excel (Visual Basic) to move through a list of markets using the -1 trigger. The code is storing LAY against my selection and setting Q2 to -1 within the same IF statement. What appears to be happening is that a LAY bet is being placed on the current market, the next market is then displayed, and a LAY bet is then placed on the next selected market as well. What do I need to do to ensure the original LAY bet is the only one placed?

Thanks
Ian S

[Captain Sensible]

Stick something in your code to reset the cell or range of cells containing LAY to "" after the bet is placed and just before setting Q2 to -1.

Alot depends on how you're triggering the LAY part of your sheet but basically you need to clear the LAY from the sheet before opening a new market

[ians1711]

Captain
Does the LAY stay there after the bet is placed? What would happen if I cleared the cell too quickly? Should I be looking for a valid bet ref before clearing the cell and triggering with -1?

Thanks so far
Ian S

[Captain Sensible]

I think BA loads the market then on the next refresh will process any bets .

So if you're firing bets via some formula it'd load the market, process any formulas then if the formulas came up with LAY it'd fire a bet on the next refresh

If you're using VBA to decide whether to place a bet then just clear the previous LAY's on the market change

Just add something like

Range("Q5:Q50").Value = ""

at the start of your VBA or even use a separate sub like

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("Q5:Q50").Value = ""

End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

to clear them on market change

[Captain Sensible]

Basically if you're adding the LAY to the sheet using VBA you'll have to clear those refs yourself