Clearing the status cells in Excel

Post Reply
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: 2686
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.
theclosedclub
Posts: 2
Joined: Mon May 17, 2021 10:19 pm

Be really careful when you try and resolve this. I set my sheet to use the timer(countdown) as the trigger to clear but as it keepsresubmitting the data each refreshI had 900 bets attempted every 200 miliseconds and ended up with a £100 charge in one day for failed bets on the betangel API. ( I ran it on a personal VPS and left it running all day)

I have found a sheet that resolves this now by having a one click function for all 30sheets and places bets but I am now working on splitting that up into a one button for each sheet .... I have just given myself a great idea on how to do that lol. My Mum always said I shouldnt talk to myself but I guess typing to myself is ok?
moolion
Posts: 6
Joined: Sat Apr 30, 2016 11:18 pm

Hello folks,

I'm interested to know how people stop this bot firing extra bets using Excel formula. I try and stop it by using an IF statement so that no commands are placed in Column L if any of the bet info Columns T to AE are not blank.

However, this bot seems to clear the status cells way quicker than than the bet info columns are updated. So what ends up happening is that my sheet fires off an extra bet before the sheet "sees" the bet info columns filled and ensures that command cells remain blank.

Any advice? Thanks.

TC.
johnD1
Posts: 4
Joined: Wed Aug 10, 2022 11:07 am

Can anyone Please suggest me how to read the column data from Excel and if the cell is empty skip that cell. thanks
User avatar
gstar1975
Posts: 615
Joined: Thu Nov 24, 2011 11:59 am

6thSense wrote:
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.
Is there a formula so when the market is suspended indefinitely, not suspended for a non runner, so the race is over, it clears the status cell, say on re-opening the spreadsheet, or after a certain amount of time has past.
Post Reply

Return to “Tips and tricks”