Help with CLOSE trigger in Excel when using multiple tabs

[MatGreenaway]

Hi, I've long successfully used Betting Assistant with Excel driving it so am fairly familiar with it.

But I am just trying something new, and trying the CLOSE trigger. I have used this successfully before and know it relies on the bet ref (column T) being populated.

The trouble is, I am loading two sets of the same data, into the same spreadsheet, albeit at different times. So 15 minutes before the off, I am loading to cell A1 and using criteria to make a back bet. And then 5 minutes before the off, the same race is loaded into cells A61 so I can do further analysis and use CLOSE to just level up for any selections I backed earlier.

The trouble is, when it loads to a new location, it doesn't bring in the original betting refs. I have tried to use the 'results' tab and some lookups and VBA to artificially populate it in column T but I find that hit and miss.

Is there a proper way to bring in betting refs when loading it from another page in Betting Assistant? I've checked the options and other triggers and just can't see a way.

The only way I can think of doing it is to do the BACKing and CLOSEing from just one page/connect to Excel, but I would have to mess about with markets so each day's meetings were in it's own page

Hope this makes sense!

Many thanks,
Mat

[alrodopial]

will a simple formula in column "T" do the job? eg T65=T5

[MatGreenaway]

No I'm afraid it won't. For one thing, data in columns T, U, V, W and X get overwritten (blanked) when the market loads. But the issue I have is what is in A1 (T5 onwards) doesn't necessarily align with what is in A61 (T65). For instance, where races can be minutes apart, another market will have loaded. I am loading one 15 minutes in advance and the other 5 minutes. Today for instance, the 14:50 at Hamilton will load at 14:35 and then the 14:58 at Newton Abbot will load at 14:43, yet from A61, at 14:45 it will load the Hamilton so at some points, different meetings will be loaded.

I can do what I need to by using VBA to record my bets elsewhere, then VBA to copy that into the relevant rows on column T just long enough for it to do the CLOSE, but I feel there must be a more straightforward way.

Thanks for the reply though

[alrodopial]

1. in theory at 5min point market will load it's own markets' matched bets (placed at 15min) in sheet "results" so you can use vba to place the corresponding bet reff. Maybe there are cases where bet reff is not stored? I don't think so because all bets placed though excel can be found
under account/transaction log. Is your vba code ok?
Gary , any ideas?
2. untick "clear bet reff at market change" to retain bet reff in clmn T
3. Maybe there is a way to do what you want only with formulas but it requires match,vlookup and defiantly loading every meeting's races into different tabs + linking them into different excel sheets

I think vba is the easiest way (preferably using the "results" sheet)

[MatGreenaway]

Thanks for the reply. I will probably go down the VBA route. I'm ok with it - self-taught... just plagiarise stuff off the web and can normally adjust to suit. I even used Chat GPT for something I needed the other day - that is really amazing how it can write VBA for you.

Thanks again and good luck with your betting!

[alrodopial]

not tested:

Code: Select all

If Target(1, 1).Address = "$A$61" Then
        If Target(1, 1) = "" Then Exit Sub
        Set wsData = ActiveSheet ' sheet getting BF data
        Set wsResults = Worksheet("Results")
        For x = 65 To wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
            horseName = wsData.Cells(x, 1)
            For xx = 2 To wsResults.Cells(wsResults.Rows.Count, "A").End(xlUp).Row
                If wsResults.Cells(xx, 2) = horseName Then
                    wsData.Cells(x, 20) = wsResults.Cells(xx, 1) ' :bet reff
                    Exit For
                End If
            Next x
        Next x
    End If

I don't know when the results sheet is updated or if the above code reenters the bet reff (after any clearance)

[MatGreenaway]

Thanks very much. I will take a look and try and use that. Many thanks,

[MatGreenaway]

I'm still struggling with this. I've not used your code yet @alrodopial, but had already captured the bet ref elsewhere and then before and during using the CLOSE trigger in the Q column, I was using VBA to put the bet references back into column T. But it instantly gets blanked again and the CLOSE therefore doesn't trigger.

I wonder if it is because I am testing something with historical data? Whether it would work in a live environment? I might see if I get chance to do a quick test tonight.

[alrodopial]

place some brake points inside your code to check when it is running, variable values , if it is running multiple times etc

https://www.excel-easy.com/vba/examples/debugging.html

[alrodopial]

maybe is better the code runs when the results sheet changes/updates (6 columns update?)
and search in sheets for same horse names
it will place the bet reff only once

[MatGreenaway]

Thanks for all the replies. I realised I was doing something silly. Must have been from something else I was doing, but where I had VBA re-copying the bet references, I also had another snip of code copying over some cells which are now blank! So I was copying the refs then blanking them straightaway!

And what I wanted to do I had done something which I thought was profitable, but I'd made an error so it was loss making so it was all for nothing anyway! At least I was testing with historical data and not losing my own cash!