Clearing the status cells in Excel

Post Reply
6thSense
Bet Angel
Bet Angel
Posts: 375
Joined: Fri Nov 30, 2012 5:02 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: 3715
Joined: Wed Apr 15, 2009 3:19 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

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

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: 4
Joined: Tue Dec 17, 2013 5:55 pm

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: 3
Joined: Tue May 27, 2014 12:55 am

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: 3
Joined: Tue May 27, 2014 12:55 am

Thank you very much nigelk, this macro solves my issue.
calvinkbee
Posts: 12
Joined: Sat Jul 29, 2017 2:24 am

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
Panrich
Posts: 3
Joined: Wed Feb 14, 2018 3:54 pm

Hi all.

New poster here and a new convert to BetAngel.

It's great to see the possibilities and have been looking at all the videos and looking at suggestions like those from this thread.

I created a spreadsheet last night that imports golf scores from MSN and then displays those beside the betfair price of the golfers in sheet 2 just as a test to see what can be done. It's not really a viable candidate for any serious betting as the MSN updates are waaaay too slow.

However, I am using Office 365 and have noticed a very strange anomaly that I cannot see reported anywhere else. Every time I start to enter a formula in any cell in sheet2 or any other sheet the data from sheet 1 populates in the appropriate cells. So just typing an = sign is enough to get all the data replicated in sheet 2 (without the formatting). I'm not sure if i am missing something obvious or if it's an issue with my version of excel, but the suggestion from post 1 here seems to indicate that it should be possible to turn this behaviour off somewhere. I've had this on both my win 10 tablet and my laptop.
Panrich
Posts: 3
Joined: Wed Feb 14, 2018 3:54 pm

I have just seen that this seems to be an issue with latest Excel versions. I can get around it for the moment by creating all my rules and formlae prior to connecting.
Tommolfc1990
Posts: 4
Joined: Mon Jun 03, 2019 7:42 pm

nigelk wrote:
Tue Jan 13, 2015 12:04 am
http://www.tradersandpunters.com/knowledge-base/

3rd article
Hi Nigel, i am in the same situation but the link you posted doesnt work any more it seems. I have managed to spread mine over 60 sheets and it clears the cells when offline, but it wont place more than one bet because for some reason it clears the full sheet of formulas when connected to BetAngel.

I have created a seperate module for each sheet:

Sub Reset_Bet15()

Application.ScreenUpdating = False

Sheets("Bet Angel (15)").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




Have any ideas?
Much appreciated
kqv91y
Posts: 30
Joined: Wed Aug 30, 2017 9:48 am

Just add this to a module then call it from your sheet ie Call ClearMerged("Bet Angel (3)")

If you don't want to clear the Bet Angel generated cells then remove the Rg1.MergeArea.ClearContents line.

Sub ClearMerged(shtName As String)

Dim Rg As Range, Rg1 As Range
Set Rg = Sheets(shtName).Range("L9:O68")
If Rg Is Nothing Then Exit Sub
For Each Rg1 In Rg
If Rg1.MergeCells Then
Rg1.MergeArea.ClearContents
Else
Rg = ""
End If
Next
End Sub
User avatar
GaryCook
Posts: 128
Joined: Sat Jan 27, 2018 6:35 pm

Not sure what I am missing but it doesnt seem to work for me. It always says, "RESET REQUUIRED.". Even though the range is all clear.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

nigelk wrote:
Tue Jan 13, 2015 12:04 am
http://www.tradersandpunters.com/knowledge-base/

3rd article
WARNING: I followed this link and was redirected from the expected address to one of suspicious makeup! It downloaded ransomeware. It was relatively easy to get out of (I used Task Master and closed all windows of the browser) but it was a pain recovering all the webpages I had open. I'm not an expert but I think Nigel's link is totally innocent because it first takes you to the intended address and then redirects. I suspect the target address has been doctored.
Post Reply

Return to “Tips and tricks”