Clearing the Status (and other) cells in Excel

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
firlandsfarm
Posts: 1296
Joined: Sat May 03, 2014 8:20 am

I'm at the stage of giving up! I just cannot see how to clear those pesky Status cells (and others). Yes there are many threads in the forum but none (duly adapted) want to work for me! I've tried them all and they refuse to clear. I'm not wanting anything fancy, I just want to clear them when Guardian/Excel move on to the next market. I'm wanting to place just 1 bet on 1 or 2 runners per market and then move on to the next.

I have a macro/subroutine (ClearCells) that works perfectly when called from the keyboard …

Code: Select all

Sub ClearCells()
'
' Clear all cells on Market Change
'
' Keyboard Shortcut: Ctrl+Shift+C
'
' ClearRunners
    Range("B9:K68").Select
    Selection.ClearContents
'
' ClearBetsData
    Range("O9:AE68").Select
    Selection.ClearContents
'
' Clear bet placement confirmations
    Range("L10,L12,L14,L16,L18,L20,L22,L24,L26,L28,L30,L32,L34,L36,L38,L40,L42,L44,L46,L48,L50,L52,L54,L56,L58,L60,L62,L64,L66,L68").Select
    Selection.ClearContents
End Sub
But when called from a subroutine monitoring a trigger cell (AI7) it gets 'stuck' at the first task (Range("B9:K68").Select) according to the debugger! My 'calling code' is ...

Code: Select all

Private Sub Worksheet_Calculate()

If Range("AI7") = "Clear_Cells" Then Call ClearCells

End Sub
Note: I only split the clearing into the three actions to try and help with debugging. And I know some say you only need to clear the Status column but on mine the redundant rows from an earlier race with more runners do not clear and it's is essential for my BOT calculations that they do! Anyway 'overclearing' should not do any harm, belt and braces and all that. :)

I would be grateful for any explanations why it's not working because I don't really want to be getting up at 03:00 just to keep manually clearing the Status and other cells.
User avatar
ShaunWhite
Posts: 6188
Joined: Sat Sep 03, 2016 3:42 am

Mmmmm have you tried prefixing the range with a sheets("SheetName") I'm wondering if it's losing track of which is the active sheet.

And maybe wrap the process with (I can't remember what it is now :roll:) but it's the commands that disable/enable calcs and refreshes. That would make sure it doesn't keep jumping out of your process to handle other sheet changes that are being triggered by updates from BA.

Not much help, but I feel your pain and any idea might lead to the fix.
User avatar
ShaunWhite
Posts: 6188
Joined: Sat Sep 03, 2016 3:42 am

.... Don't forget to clear A17 as the last step or it'll get itself in a loop, and then you'll regret not having a DoEvents command in there somewhere otherwise you won't be able to break. I hate excel for that, it's gets in such a tight neverending loop sometimes that it doesn't look for you smashing away on break or escape keys trying to get the damn thing to stop....unless it executes a DoEvents command at some stage.
firlandsfarm
Posts: 1296
Joined: Sat May 03, 2014 8:20 am

ShaunWhite wrote:
Sun May 17, 2020 7:00 pm
.... Don't forget to clear A17 as the last step or it'll get itself in a loop, and then you'll regret not having a DoEvents command in there somewhere otherwise you won't be able to break. I hate excel for that, it's gets in such a tight neverending loop sometimes that it doesn't look for you smashing away on break or escape keys trying to get the damn thing to stop....unless it executes a DoEvents command at some stage.
Thanks Shaun and that could explain some strange behaviour I've been experiencing while 'playing' with various variations of code … Sometimes it's just crashed out on me, other times when I move the focus from cell to cell the 'cell contents' box just below the ribbons doesn't change but if I then double click on a cell with a formula while the cell contents box stays unchanged (showing the contents of another, previous, cell) the actual cell opens and show the formula for editing! I'll check the loop issues first thing.

And I'll do the tidying up as you suggest. I'm not really a VBA guy but I have 40 year old coding experience (BASIC) so when I search around the forums or general VBA forums I can generally find and adapt suggestions to what I need but it means I have no good housekeeping skills! I assume by lack of a specific comment the code itself is good and it's really a matter of finding how to present it.
PeterLe
Posts: 3586
Joined: Wed Apr 15, 2009 3:19 pm

Hi Firlands
I haven't used this lately, but this is the code from one of my excel sheets
It only clears the status cells, but you can probably adapt it?
Regards
Peter

Private Sub Worksheet_Calculate()
Dim i As Integer
For i = 9 To 67 Step 2
If Range("O" & i).Value = "PLACED" Or Range("O" & i).Value = "MARKET_SUSPENDED" Or Range("O" & i).Value = "ERROR" Or Range("O" & i).Value = "FAILED" Then _
Range("O" & i).ClearContents
Next i
Dim j As Integer
If Range("H" & 1).Value = "Suspended" Then
For j = 9 To 67 Step 2
If Range("O" & j).Value = "PLACED" Or Range("O" & j).Value = "MARKET_SUSPENDED" Or Range("O" & j).Value = "PLACING" Or Range("O" & j).Value = "PLACED_KILL_PENDING" Or Range("O" & j).Value = "ERROR" Or Range("O" & j).Value = "FAILED" Then _
Range("O" & j).ClearContents
Next j
For k = 4 To 6 Step 2
If Range("O" & k).Value = "OK" Or Range("O" & j).Value = "FAILED" Then _
Range("O" & k).ClearContents
Next k
End If
End Sub
firlandsfarm
Posts: 1296
Joined: Sat May 03, 2014 8:20 am

PeterLe wrote:
Sun May 17, 2020 10:43 pm
Hi Firlands
I haven't used this lately, but this is the code from one of my excel sheets
It only clears the status cells, but you can probably adapt it?
Regards
Peter
Hi Peter and thanks for sharing the code, I've 'banked' it because after Shaun's comment about not clearing my AI7 trigger cell maybe causing a loop and the troubles I encountered first thing this morning I have decided that the reason for the ClearCells() sub not working was because of 'interference' from the 'calling' sub and AI7. I had troubles right at the start this morning even when going the "Ctrl - Shift - C" route. So I deleted the 'calling' sub and at least the "Ctrl - Shift - C" manual workaround is OK again.

I'm not 100% clear what to do with preventing the AI7 cell value causing the subroutine to loop and will comment on that in a further response to Shaun's advice but it seems to me what I need is to somehow run the ClearCells subroutine when the B1 cell, the Current Market cell, changes. I will record my findings/frustrations in a separate post below but I still can't understand why BA was designed as an automation pack that makes it as difficult as possible to run automatically! To me this is a massive bug and should be sorted ASAP!
Atho55
Posts: 405
Joined: Tue Oct 06, 2015 1:37 pm

I agree with your comments Firlands. It should not be up to the Forum to resolve issues with the BA Excel interface re Clearing Status Cells yet they seem happy for this issue to continually rumble on year upon year with almost zero comments of late what the changes/outcome might be in the future. Suppose we`ll be getting more Servant bells and whistles sooner.... rant over.
sa7med
Posts: 782
Joined: Thu May 18, 2017 8:01 am

firlands, as shaun said if it's working when you call it manually on an activesheet then it's probably not pointing to the right place and that's why it's infinitely looping. Why not feed your sub the worksheet that you want to clear. IE

Code: Select all

		ClearStatus(ws as worksheet)
			' ClearRunners
    			ws.Range("B9:K68").Select
   			Selection.ClearContents
That way when you call your sub from the worksheet you can call it as ClearStatus(worksheet("Bet Angel")). Don't know if that would work but worth a try.
Also, you can try and add a counter to trigger every time the if statement is run and add counter <= a number to your if statement so it doesnt go wild. Hope that helps.

EDIT: Another option would just be to copy the code over into the sheets themselves into your worksheet calculate event, rather than calling it.
Atho55
Posts: 405
Joined: Tue Oct 06, 2015 1:37 pm

VBA.png
You do not have the required permissions to view the files attached to this post.
Anbell
Posts: 783
Joined: Fri Apr 05, 2019 2:31 am

It looks like you're getting some advice from some of the old-hands here, and I'm just a hack

But (if I understand your issue) from what I've read you should probably stay away from SELECT. It is sluggish and unnecessary.

I call the following all day every day via keyboard shortcut without any problems:

(i'd give credit to someone here who authored BetAngel_Clean_Sheets but can't find the source)

Code: Select all

Sub BetAngel_Clean_Sheets()


Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    Application.screenUpdating = False

If Left(ws.Name, 9) = "Bet Angel" Then
Sheets(ws.Name).Range("B1").ClearContents
Sheets(ws.Name).Range("L9:L55") = ""
Sheets(ws.Name).Range("B9:s55") = ""

End If
Sheet6.Range("F9:F55").ClearContents
Sheet6.Range("P2:Q6").ClearContents
Sheet6.Range("Q9:R55").ClearContents
Sheet6.Range("U9:V55").ClearContents
Sheet6.Range("Y9:AQ55").ClearContents

Sheet6.Range("U58:V59").Value = ""
Sheet6.Range("R1").Value = ""
Sheet6.Range("R58:R59").Value = ""
Sheet6.Range("R1").Value = ""


Sheet4.Range("H4") = ""
Sheet13.Range("G1:G12") = ""
Sheet6.Range("R1").Value = ""

Next

    Debug.Print Format(Now(), "hh-mm-ss") & "   BetAngel_Clean_Sheets"
        
        
            Application.screenUpdating = True

End Sub


I've got my own personal ranges here that you won't need, but it seems that you can use either format:

Sheets(ws.Name).Range("xx:yy") = ""
or
SheetY.Range("x9:x55").ClearContents

Or perhaps I've misunderstood
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”