Clearing the Status (and other) cells in Excel

Post Reply
User avatar
firlandsfarm
Posts: 2688
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: 9731
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: 9731
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.
User avatar
firlandsfarm
Posts: 2688
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: 3715
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
User avatar
firlandsfarm
Posts: 2688
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: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

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: 800
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: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

VBA.png
You do not have the required permissions to view the files attached to this post.
Anbell
Posts: 2004
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
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Guys, thanks for all the inputs, all different so much to try. And thank you for the various suggestions for subroutines clearing the cells but as I've said I have a sub for that and if works great if fired from the keyboard. The problem is getting it to fire from within VBA.

I will have a look in the morning at the suggestions. All I want to do is clear the old runner specific data when the worksheet switches to a new market. I don't want to make multiple bets within the same market … just one bet and move on to the next market. Why oh why does BA think I would want to keep the out of date redundant data referring to a race just gone and apply it to the next race? What has a bet placed on the 3rd runner in the 2:30 got to do with the 3rd runner in the 3:00? It's madness!

It's a problem I've always had every time I try to fire a cell clearing sub … this is my 3rd attempt at an Excel BOT and the same as the other two times it does not work (for me)! I've tried using "Sheets("Bet Angel P").Activate" but that didn't make any difference, still nothing! I've tried 3 suggested code sets from the Internet but they didn't do anything. I'm now going to try and set up a dynamic system timer and compare that with the Event Time cell to ID when that time has passed and clear the cells then but my concern is that online articles suggest that can be resource heavy … something we don't want. As I said in my last post I just can't see why BA put this complication in what they claim is automation software. All it would need is a checkbox cell asking if the user wants the Status and other race specific cells to be retained or cleared when the market changes … It's not Saturn 5 science!
rostov
Posts: 50
Joined: Mon Jul 02, 2012 3:45 pm

Try this in the sheet tab.

Private Sub Worksheet_Calculate()
Static OldVal As Variant

Application.EnableEvents = False

If Range("$B$1").Value <> OldVal Then
OldVal = Range("$B$1").Value
Call ClearCells
End If

Application.EnableEvents = True

End Sub
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

Atho55 wrote:
Mon May 18, 2020 10:35 am
Image
lol -just noticed your little edit on the red bubble on the cover: Clear status Cells the EASY way
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

jimibt wrote:
Tue May 19, 2020 3:04 pm
lol -just noticed your little edit on the red bubble on the cover: Clear status Cells the EASY way
Atho, that one passed me by!! :lol: :lol: :lol:
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

My photo editing skills obviously better than my VBA skills...
Post Reply

Return to “Tips and tricks”