Gruss – Excel problem

Have you tried putting =Market!G20 in a separate column to the right and then linking that cell to the price cell?

I tried that Dave but it didn't make any difference.

Are you sure the price is a valid price within the Betfair increments, if so maybe try +Market!G20 as that should force it to be treated as a number

Or try =ROUNDUP(Market!G20,0) that will round to whole numbers (obv no use for going to war, but might identify the problem)

I have a sheet that does exactly what you are trying to do but I use VLOOKUP to get the price from the other sheet. The price doesn't need to be in Betfair increments , Gruss sorts that out , mine have three decimal places.

I presume you are not typing the quotes around “=Market!G20” - that would be treated as text.

The price doesn't need to be in Betfair increments , Gruss sorts that out , mine have three decimal places.  Interesting, I'm sure I tried something like "back 1.1 x last price matched" and it didn't work, must've messed up elsewhere

Thanks for the replies – have tried/checked all suggestions to no avail.

So I then tried to make the trigger price cell fetch the price using the same VLookup formula as the original cell. It populates the cell with the correct price but then fires the lay in at the wrong price! (which appears to be the current odds price).

I originally based the spreadsheet on one that I downloaded from Gruss (“multiple Triggers” sample spreadsheet) so I didn’t write the code. Although it is simple looking, I am not very good with these things so it is obviously doing something that I didn’t realise.

Not sure if the above makes sense! All I am trying to do is fire in a series of multiple lays at prices taken from another worksheet - I am obviously making it more difficult than it should be.................

So the odds are still "current" per the Market sheet when it fires the bet?  Or is the Market sheet being constantly updated too?  (thus what were the odds you wanted to lay no longer are...)

I realise on re-reading this that I haven’t made what I am trying to do very clear.

I am using the sample “multiple Triggers” spreadsheet from the Gruss website.

This has two worksheets, “Market” and “Triggers”.

The “Market” worksheet logs the usual market information from Gruss and has column Y labelled as “Trigger Step”.

The “Triggers” worksheet has three columns, “Trigger”, “Odds” and “Stake” with each following row showing your intended bet (eg. A2= “LAY”, B2= “1.41” and C2= “100” – so the first bet would be to Lay @ the price of 1.41 for £100).

The code is written so that when you enter a “1” in the Y column it fires in the bets from the “Triggers” worksheet for that team.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Integer, triggerRow As Integer
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        With ThisWorkbook.Worksheets("Triggers")
            For r = 5 To 54
                If Cells(r, 25)  "" Then
                    triggerRow = Cells(r, 25) + 1
                    .Range(.Cells(triggerRow, 1), .Cells(triggerRow, 3)).Copy Range(Cells(r, 17), Cells(r, 19))
                    If Cells(r, 17) = "CANCEL-ALL" Then Cells(r, 20) = "ALL" Else Cells(r, 20) = ""
                    If Cells(r, 17) = "" Then Cells(r, 25) = "" Else Cells(r, 25) = triggerRow
                End If
            Next
        End With
        Application.EnableEvents = True
    End If
End Sub


If I simply type the odds into the “Triggers” worksheet and then trigger the code, the bets are placed with no problem. However, If I try to populate the “Odds” column in a different way by either making the cell = a different cell (=Market!G20) or directly entering the VLOOKUP formula that Market!G20 uses in the first place then the bets are not placed with the Bet Ref column showing “INVALID_PRICE” when trying to enter the bets.

The table used for the VLOOKUP only contains valid Betfair increments.

does it work if you just put eg = aa20 in the cell and write 2.5 in aa20?  If it doesn't it would appear that the cell will only accept manual input ie it always reads "=aa20" and not the value that us humans can see?

No, that doesn't work Dave.

The problem is, why will it only accept manual input?

btw on the standard gruss spreadsheet with last price in the O column, trigger in the Q column and odds in the R, I have put =05 in the odds cell and it works fine.

I have found a small workaround for now by writing a macro to copy and paste the cell values into the appropriate cells on the “Triggers” worksheet – all it means is an extra button press before triggering the LAYS which is still much quicker and I only want to use the spreadsheet when I am actually sat in front of it so its no problem.

It still seems strange though so I will post the problem on the Gruss forum (the reason I posted on here first was that there doesn’t seem much activity over there.

Thanks for the replies.

Cheers please update this thread if you find out the problem as it does seem odd

Looks like the trigger code is copying cells in columns A, B and C to Q, R and S so the Odds values that are considered invalid would actually be in column R. I suspect that the copy is modifying the formula - have you tried using "=Market!$G$20" in column B so it always points to a fixed cell reference ?

Haven't seen the full sheet but does it use VBA to populate the odds columns, if they're blank when it triggers it'd give the error or even if it's taking that data as text rather than numbers. Might just need the code amend to things like .Cells(triggerRow, 1).value . Hard to see where it's going wrong without the actual sheet but becuase figures are being gathered and entered by VBA it's not as simple as it first seemed

mja
Your fixed cell reference solution has worked! Cheers!

Thanks agian for all the other replies.