Excel - cell Q2

[jockyson]

I want to use cell Q2 to move the Quick Pick list on to the next race, based on some criteria in the spreadsheet.

Is there a simple way to do this? The contents of cell Q2 currently get deleted when the new market is selected.

Is there something I can do to retain a formula, or reference to another cell - i.e. prevent the contents of Q2 being deleted?

I thought maybe protecting the cell from being edited (by Gruss) might work? Or would it need a macro or VB code?

Any help appreciated. JP.

[fogosan]

Hi,

The contents of cell Q2 currently get deleted when the new market is selected - this is correct, otherwise you would go thru whole quick pick list.

I think you need to wait for a moment after movement to the next race.
Try to search forum, I am sure there is more advices, also with VBA code...

[jockyson]

Cheers, yes I guess it gets cleared to prevent a constant loop. But it's simple enough to link to the timer so it only gets populated every 10 secs, or whatever.

Have searched for other similar posts. But they refer to VBA. I was hoping there would be some simple little trick though that wouldn't require any coding.

[fogosan]

I do not know how to do it without VBA.
Possibility is in combination of VBA and your conditions.

In this topic you can find macro which will move to the next race once value in AJ5 is equal 1.
http://www.gruss-software.co.uk/forum/viewtopic.php?t=5672
Everything you need to do is allow macros and copy that code to your module (don`t forget to change workbook name).

[alrodopial]

The contents of cell Q2 currently get deleted when the new market is selected.

???

For me there are not. Formula in Q2 stays alive always.
Maybe you have ticked something in BA?
Garry / Mark ?

[GaryRussell]

The contents of cell Q2 currently get deleted when the new market is selected.

???

For me there are not. Formula in Q2 stays alive always.
Maybe you have ticked something in BA?
Garry / Mark ?

Did you tick "Enabled triggered" betting when linking to Excel?

What is your formula? It might not be recognising the trigger. If it is inserting -1 as numeric then try it as text, ie. "-1" and see if that makes a difference.

[osknows]

you can also add a formula back in using vba eg

.range("A1").Formula = "=A2*B2"

[jockyson]

Apologies - found it works fine with the Betfair version. I have been working on the Betdaq beta version - it has a different set-up. In that, cell Q2 gets cleared. Any idea how I can get round it when using the Betdaq beta?

[GaryRussell]

Apologies - found it works fine with the Betfair version. I have been working on the Betdaq beta version - it has a different set-up. In that, cell Q2 gets cleared. Any idea how I can get round it when using the Betdaq beta?

I'm not sure what you mean. It should work the same in the Betdaq version, ie. in both versions the cell is cleared after -1 is detected. Are you saying it is cleared in one version and not the other, and can you clarify in which version it is not cleared?

It would also help if you could send a spreadsheet to admin@gruss-software.co.uk that demonstrates the problem. We will be able to solve the problem quicker. It doesn't need to contain any of the code or formulas to trigger bets only the code that inserts the trigger into Q2.

[jockyson]

Thanks Gary. Last night I tried it out on the Betfair version, and it appeared that 'Refresh Rate' did not appear in cell P2, and that cell Q2 did not get cleared after using -1.

But this morning, with UK racing Q2 is getting cleared. Both Betfair and Betdaq versions are the same, sorry.

I will send you the spreadsheet. Yes, I understand I just need a bit of code to insert the trigger into Q2, but that's a bit beyond my basic excel skills - so any assistance would be gratefully received. JP.

[osknows]

Hi jockyson,

This is Gary's code slightly adapted

Code: Select all

Dim marketChanging As Boolean, currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False


With ThisWorkbook.Sheets(Target.Worksheet.Name)
   If Not marketChanging And .Range("THIS CELL").Value = "THIS VALUE" Then
      marketChanging = True
      currentMarket = .Range("A1").Value
      .Range("Q2").Value = -1
   Else
      If .Range("A1").Value <> currentMarket Then marketChanging = False
      .Range("Q2").Formula = "=ADD YOUR FORMULA"
   End If
End With

Application.EnableEvents = True

End Sub


Add the code to the SHEET object and change the following

.Range("THIS CELL").Value = "THIS VALUE" - replace THIS CELL with the cell to trigger the change, THIS VALUE is the value of the cell. If the cell is numeric then remove the quotes from "THIS VALUE"

eg, if you want the market to forward when cell T3 = 1 then .Range("T3").Value = 1

.Range("Q2").Formula = "=ADD YOUR FORMULA" - replace =ADD YOUR FORMULA with the formula you want in cell Q2