Gruss – Excel problem

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

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 presume you are not typing the quotes around “=Market!G20” - that would be treated as text.

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.................

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.

No, that doesn't work Dave.

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

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.

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 ?

mja
Your fixed cell reference solution has worked! Cheers!

Thanks agian for all the other replies.