Clearing the status cells in Excel

Example spreadsheets and comments on example spreadsheets.
Post Reply
6thSense
Bet Angel
Bet Angel
Posts: 375
Joined: Fri Nov 30, 2012 5:02 pm

Thu Dec 20, 2012 3:12 pm

Clearing The Status Cells

I thought I would post up a spreadsheet that automatically does what a lot of people want - clear the status field once a bet has been placed.

There are a few ways of doing this but I am going to go down the VBA route and tie this with a reset flag.

In a cell somewhere on your worksheet, say sheet 2, cell A1 (never use the Bet Angel worksheet for calculations) type in the following:

Code: Select all

=IF(COUNTIF('Bet Angel'!O6:O67,"")<61,"RESET REQUIRED","")
This means the cell will display 'RESET REQUIRED' if any cell from O6 through to O67 is populated with anything.

You can then create a Sub in a module to clear the cells. Use this code:

Code: Select all

Sub Reset_Bet()
    
    Application.ScreenUpdating = False

    Sheets("Bet Angel").Select

Range("O6,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").Activate

    Selection.ClearContents
    
    Range("A1").Select
         
    Application.ScreenUpdating = True

End Sub
Then call this code whenever something the reset flag is set. This can be done by entering the following in the Sheet2 Object

Code: Select all

Private Sub Worksheet_Calculate()

If Range("A1") = "RESET REQUIRED" Then Call Reset_Bet

End Sub
This will clear the cells whenever there is a calculation made on the sheet. And when connected to a Bet Angel market, Excel is effectively automatically performing calculations constantly.

The attached sheet has all the above. Connect it to Bet Angel and try it out.
You do not have the required permissions to view the files attached to this post.

PeterLe
Posts: 3280
Joined: Wed Apr 15, 2009 3:19 pm

Thu Dec 20, 2012 6:26 pm

Hi
Just a friendly word of warning..
unless you have carefully designed your spreadsheet you could end up submitting many bets into the market...
In my early days I remember sitting in Dublin Airport connected to my VPS and watching about 600 bets (pre race) being submitted.. :o
I was the mad bomber that day and ended up pushing the price down!
I quickly greened up and made about £70 I recall...

Just be careful (or move most of your money into your Aus wallet whilst you test...)
Regards
Peter

6thSense
Bet Angel
Bet Angel
Posts: 375
Joined: Fri Nov 30, 2012 5:02 pm

Thu Dec 20, 2012 7:10 pm

Good point.

This spreadsheet has the capability to cancel the status cells many times per second whilst connected to Bet Angel.

So to anyone who wants to use this method you need to make sure your bet triggers need to be written in such a way to take this into account.

Corine
Posts: 11
Joined: Thu Sep 27, 2012 12:50 am

Sun Apr 28, 2013 6:15 pm

I have used this while testing but it does fire lots of bets into the market on whatever I trigger a bet on. Is there any way to modify the sheet so as it will only fire the one bet into the market and then stop altogether.
Thanks in advance

Corine

Wildefish
Posts: 2
Joined: Tue Dec 17, 2013 5:55 pm

Mon Dec 23, 2013 12:55 am

6thsense,

Many thanks for this and I can get it to work on the first Bet Angel Sheet. As I know nothing about VB, what would I have to change to to enable it to do the same for multiple sheets ("Bet Angel (2)" & "Bet Angel (3)")?
I placed the macros for these in sheet 2 in A2 and A3 and I can get the "reset required" to appear.

I tried experimenting with VB and guessing how to add 2 and 3, but it didnt work.

Cheers

Rob

Systemat
Posts: 2
Joined: Tue May 27, 2014 12:55 am

Mon Jan 12, 2015 7:43 pm

Hi,

I have been playing around with the BetAngel_Clear_Status.xls 6thSense produced but like Wildefish I've been unable to replicate the clear status cell results simultaneously across multiple worksheets. If anyone has any advice on how to amend the code above to achieve this I’d be really interested to hear your thoughts.

Thanks


Systemat
Posts: 2
Joined: Tue May 27, 2014 12:55 am

Wed Jan 14, 2015 3:44 pm

Thank you very much nigelk, this macro solves my issue.

calvinkbee
Posts: 11
Joined: Sat Jul 29, 2017 2:24 am

Tue Dec 26, 2017 7:02 pm

Thank you for posting this, it is really useful :)
One question, is it possible to alter the rate at which this will refresh i.e. to only refresh once every 2 seconds?
Thanks in advance

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest