Clearing the Bet Status Cells in Excel

Help improve Bet Angel.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Atho55 wrote:
Tue May 19, 2020 2:58 pm
BA, if you already have solutions of many kinds assuming you have resolved the requests, why not bundle them ALL into a folder with a description of what each one does so the user can select the one that looks to be the best fit.
Good suggestion Atho, just as Guardian and Servant rules are posted.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 3:26 pm
Here's a macro we have used that detects market change and clears the cells.

Code: Select all

Dim OldMarketId As String

Private Sub Worksheet_Change(ByVal Target As Range)
    ' cells C2:C6 will be the last range to be updated during the update process
    If Target.Address = "$C$2:$C$6" Then
        If Range("A1") <> OldMarketId Then
            ' new market, so remember market id
            OldMarketId = Range("A1")
            ' clear the global cells
            Range("L6:O6").ClearContents
            ' clear betting command
            Range("L9,L11,L13,L15,L17,L19,L21,L23,L25,L27,L29,L31,L33,L35,L37,L39,L41,L43,L45,L47,L49,L51,L53,L55,L57,L59,L61,L63,L65,L67").ClearContents
            ' clear odds
            Range("M9,M11,M13,M15,M17,M19,M21,M23,M25,M27,M29,M31,M33,M35,M37,M39,M41,M43,M45,M47,M49,M51,M53,M55,M57,M59,M61,M63,M65,M67").ClearContents
            ' clear stakes
            Range("N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33,N35,N37,N39,N41,N43,N45,N47,N49,N51,N53,N55,N57,N59,N61,N63,N65,N67").ClearContents
            ' clear status
            Range("O9,O11,O13,O15,O17,O19,O21,O23,O25,O27,O29,O31,O33,O35,O37,O39,O41,O43,O45,O47,O49,O51,O53,O55,O57,O59,O61,O63,O65,O67").ClearContents
        End If
    End If
End Sub
Thanks Guys, I'll give it a go … but with "L", "M" and "N" removed! :)
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Whilst on the subject of clearing cells is there any reason why the selections rows are not always cleared?
SelectionsRows.JPG
You do not have the required permissions to view the files attached to this post.
Bet Angel
Bet Angel
Bet Angel
Posts: 3999
Joined: Tue Apr 14, 2009 3:47 pm

firlandsfarm wrote:
Tue May 19, 2020 3:40 pm
Whilst on the subject of clearing cells is there any reason why the selections rows are not always cleared?
Probably Excel locking out cell updates when a market change occurs. If you're live editing something in Excel while it's linked to another application, then Excel can prevent external updates. Also if there's a really slow macro running in Excel then that can block updates too. Nothing we can do about that in the Bet Angel code.
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

Firstly, BA, thanks for the Macro.

Secondly, you have assumed incorrectly that I have a working knowledge of VBA otherwise you would have:

1) included instructions on how to install it (least desireable)
2) attached a spreadsheet with it already contained within it.

Do you now see what the issue is. Not everbody sits looking at code everyday or has a desire to do so which unfortunately places the onus back on yourselves if you wish to satisfy the needs of some.

The end...
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 3:50 pm
firlandsfarm wrote:
Tue May 19, 2020 3:40 pm
Whilst on the subject of clearing cells is there any reason why the selections rows are not always cleared?
Probably Excel locking out cell updates when a market change occurs. If you're live editing something in Excel while it's linked to another application, then Excel can prevent external updates. Also if there's a really slow macro running in Excel then that can block updates too. Nothing we can do about that in the Bet Angel code.
Understood but that's why I included the "B" - "K" cells in my 'clearing' sub because anything left in there from a previous market really screws up my calculations for the next market.
Bet Angel
Bet Angel
Bet Angel
Posts: 3999
Joined: Tue Apr 14, 2009 3:47 pm

Atho55 wrote:
Tue May 19, 2020 4:59 pm
attached a spreadsheet with it already contained within it.
Here's the template spreadsheet with the macro attached, but it is intended as an example - if it clears more or less than required then you'll need to use the macro editor built into Excel to adapt it. It's worth investing a bit of time learning the basics of the macro editor as it unlocks so much potential in Excel. You don't need coding experience, once you can navigate around the macro editor it's usually just a case of copy/pasting snippets of code from the internet.
You do not have the required permissions to view the files attached to this post.
Bet Angel
Bet Angel
Bet Angel
Posts: 3999
Joined: Tue Apr 14, 2009 3:47 pm

firlandsfarm wrote:
Tue May 19, 2020 5:44 pm
Understood but that's why I included the "B" - "K" cells in my 'clearing' sub because anything left in there from a previous market really screws up my calculations for the next market.
There's no harm in adding lines to clear columns B to K to that macro I gave you earlier. It'll only trigger when the market id changes, so runs only once per market change, so you're free to adapt the macro to do whatever is required.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 5:54 pm
There's no harm in adding lines to clear columns B to K to that macro I gave you earlier. It'll only trigger when the market id changes, so runs only once per market change, so you're free to adapt the macro to do whatever is required.
Yes I know, I had every intention of adapting it to my own needs thanks that's why I said I would remove the references to "L", "M" and "N" cells! :) My point wasn't to ask if I could it was to say that although people tell me the Selections cells are cleared when the Market changes that's not always so and additional action is required to make sure. You will see that the sub I posted clears everything except the "L", "M" and "N" cells!
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 5:50 pm
Here's the template spreadsheet with the macro attached, but it is intended as an example - if it clears more or less than required then you'll need to use the macro editor built into Excel to adapt it. It's worth investing a bit of time learning the basics of the macro editor as it unlocks so much potential in Excel. You don't need coding experience, once you can navigate around the macro editor it's usually just a case of copy/pasting snippets of code from the internet.
Hi all, just as a note of caution … the spreadsheet provided by BA above will clear all the "L","M" and "N" cells as well as the "O" (Bet Status) cells. So if you use it as is you will lose any entry (amount or formula) for the Bet Placing, Odds and Stakes. Should you not want to do that then right click on the worksheet tab and choose View Code when you should see the code. You will notice some is coloured Green, these are the coder's notes describing what the code following does. You will see they refer to ...

' clear the global cells
' clear betting command
' clear odds
' clear stakes
' clear status

Each is followed by a "Range(…).ClearContents" command … remove the commands for the cells you DO NOT want to clear. So if you only want to clear the "O" (Bet Status) cells then remove all the other ClearContents commands leaving just the "clear status" command.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 3:26 pm
Here's a macro we have used that detects market change and clears the cells.

Code: Select all

Dim OldMarketId As String

Private Sub Worksheet_Change(ByVal Target As Range)
    ' cells C2:C6 will be the last range to be updated during the update process
    If Target.Address = "$C$2:$C$6" Then
        If Range("A1") <> OldMarketId Then
            ' new market, so remember market id
            OldMarketId = Range("A1")
            ' clear the global cells
            Range("L6:O6").ClearContents
            ' clear betting command
            Range("L9,L11,L13,L15,L17,L19,L21,L23,L25,L27,L29,L31,L33,L35,L37,L39,L41,L43,L45,L47,L49,L51,L53,L55,L57,L59,L61,L63,L65,L67").ClearContents
            ' clear odds
            Range("M9,M11,M13,M15,M17,M19,M21,M23,M25,M27,M29,M31,M33,M35,M37,M39,M41,M43,M45,M47,M49,M51,M53,M55,M57,M59,M61,M63,M65,M67").ClearContents
            ' clear stakes
            Range("N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33,N35,N37,N39,N41,N43,N45,N47,N49,N51,N53,N55,N57,N59,N61,N63,N65,N67").ClearContents
            ' clear status
            Range("O9,O11,O13,O15,O17,O19,O21,O23,O25,O27,O29,O31,O33,O35,O37,O39,O41,O43,O45,O47,O49,O51,O53,O55,O57,O59,O61,O63,O65,O67").ClearContents
        End If
    End If
End Sub
That's working fine this morning thanks (as does the one from Rostov I posted here earlier). I've taken out clearing the Bet Placement, Odds and Stakes cells and didn't forget to remove L6 :), amended "O" to sweep all the way across to AE ("O9:AE68") and I've added "B9:K68" just to make sure everything is cleared. I can see your logic and will use that going forward as I learn more. Again, thanks but … I really do think you should 'formalise' this as a facility. :)
dbowdler
Posts: 1
Joined: Fri Jul 01, 2022 2:45 pm

Separating column O from column L/M/N so that column O is automatically cleared when the market changes without impacting columns L/M/N would be ideal.
User avatar
getagrip68
Posts: 25
Joined: Thu Oct 14, 2021 5:56 pm

It's pretty easy to create a button to clear cells by following the instructions on the page below, just record deleting specific cells rather than a whole sheet:

https://turbofuture.com/computers/Creat ... -Worksheet
Post Reply

Return to “Suggestions”