Auto clearing status cells

Example spreadsheets and comments on example spreadsheets.
bamthwok
Posts: 10
Joined: Thu Apr 30, 2009 7:14 pm
Location: Manila/ Philippines

Hi,

Can someone please help and advise how to make this idea work for the Betangel multiple spreadsheet.

Any advise is highly appreciated.

Cheers,
bamthwok
tony63
Posts: 22
Joined: Wed Mar 16, 2016 12:14 pm

Hello.

I downloaded the spreadsheet that auto clears the cells yesterday and was planning to trial it today.

I know you are aware of this but is has probably slipped your mind, I am one of, I suspect, many people that are not able to sit and watch the markets but HAVE to rely on automation.

As soon as I open an unrelated worksheet I get "subscript out of range". When I go into the debugger the problem is

Worksheets("Sheet2").Range("a4") = 1

I can muddle my way through visual basic but I don't know enough to correct it. It appears that the routine is applying to the whole of Excel rather that the specific workbook.

Thanks.
tony63
Posts: 22
Joined: Wed Mar 16, 2016 12:14 pm

Sorry - Should have said. I'm working on the latest version of Excel.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

If you have 2 or more workbooks open vba gets a bit confused as to which workbook you are referring to.

It's likely that the code refers only to its own workbook, which hasn't got a sheet named "Sheet2".

You'll need to fully qualify each reference to each workbook.

If I have 1 workbook open, Twb and Owb will give the same result. If I have 2 workbooks open, and the 2nd is the ACTIVE (the one you are looking at), Owb will return a different name.

Code: Select all

Sub test()


Dim Twb As Workbook, Owb As Workbook

Set Twb = ThisWorkbook
Set Owb = ActiveWorkbook

MsgBox Owb.Name


End Sub
This can refer to either workbook, (but will fail if it can't find a sheet named sheet1.)

Code: Select all

Owb.Sheets("Sheet1").Range("A2") = "1"
This will always refer to the workbook in which the code resides.(Again will fail if no sheet named sheet1).

Code: Select all

Twb.Sheets("Sheet1").Range("A2") = "1"
andrewcichello
Posts: 6
Joined: Tue Nov 22, 2016 10:15 am

Hi Everyone,

The attached file works for me however I'd like to use this with multiple sheets. Does anyone have a worksheet clearing status cells on multiple sheets? Or a work around? I want to bet on many races per day and clear the status cells every 60 seconds.

Any help would be greatly appreciated.

Cheers,

Andrew
You do not have the required permissions to view the files attached to this post.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

This will clear all status cells on any sheets beginning with the name "Bet Angel"

Note that since there are multiple sheets the formula in J2 in not relevant as it only points to 1 sheet.

Edit. Posted wrong sheet. Below is correct.
You do not have the required permissions to view the files attached to this post.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

We all have specific requirements for when we want to clear status. So rather than wait for others to give you a part solution build something yourself. Write conditional statments based on spreadsheet data within a worksheet_calculate event. This allows you to trigger a cleardown when you define. This built in function is called whenever a formula calculation is triggered. So in a single cell have a formula which sums up the countdown,odds and unmatched money. This will ensure continous streaming of triggers on any key change anywhere from miliseconds to a second.

Secondly, to avoid unwanted bets when clearing status, also clear down one of the other fields i.e. stake or bet command.

Thirdly, for multi-spreadSheet always uss me.cells commands rather than sheet1.cells (sheet2..3..4..5 etc). This allows you to copy/replicate the sheet with the code and it will.work in each specific sheet.
Callumbet97
Posts: 2
Joined: Wed Apr 12, 2017 11:06 am

alto wrote:
Sun Jul 21, 2013 8:26 pm
Hi.
I've got the spreadsheet working (IE, it clears the cells when I click the Start button) but it doesn't continue to clear every 10 seconds thereafter.

Is this what it is supposed to do? Am I getting something wrong?
I'm having the same issue.
Anyone have come up with an solution?

Thanks
Also having the same issue. Any solution?
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

In H4 the countdown timer is converted into seconds.

In I4 the mod function divides by ten for your 10 second timer.

J4 will increase by 1 each time when the divisor is zero.

Note: A 1/2 second delay (sleep 500) has been added to stop the routine sometimes firing twice. Depending to the complexity of your code and how long it takes to run, I would imagine that this could be removed if required, but haven't tried it.

Replace the "Range("j4") = Range("j4") + 1" code with your clear status subroutine

Cheers,Nigel.
BetAngel_10 seconds.7z
You do not have the required permissions to view the files attached to this post.
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Good evening gents,

I thought I'd ask the question in this thread to keep everything tidy and relevant. Would anybody be kind enough to spare a code for clearing the status cell, instead of going be a timer has anybody done it using the worksheet_change function using VBA?

My strategy will require quite quickly clearing cells you see. Because of this though, I have another problem. If for example I put the following in cells L9, M9 and N9:
BACK, 4.0, 2

But then my formula changes L9 from BACK to " " before the bet has had chance to be placed, I get left with 'OK' or even just 'ERROR'. Is there a way, again using VBA (perhaps intertwined with the clear status worksheet_change function) that I can have a spreadsheet keep the order in the instruction field long enough for it to go through?

Extra info:
BACK Triggers - D31:D36 (For 6 seperate runners)
LAY Trigger - E31:E36 (Ditto)
BACK CLOSE Trigger - F31:F36 (And again)
LAY CLOSE Trigger - G31:G36 (You guessed it!)

So to reiterate, Cell D31 says "BACK" for horse 1 but can change quickly (before the order hits the market) and then after it's been placed, I'd like to immediately clear the status cell please. I have precautions in place with the triggers to avoid multiple bets firing in already. If anybody could be so kind as to share some code or point me in the right direction it would massively help me out.

Cheers lads!
Callum :)
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Hi Callum

You could monitor column O to see if the bet has been placed, then clear the relevent row in col L. Something along the lines of

Code: Select all

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 15 And Target = "Placed" Then

rn = Target.Row

Cells(rn, 12) = ""

Target = ""

End If

End Sub
The above will clear both the report and status cells. Change to suit. Don't forget to disable/enable events if needed

Cheers, Nigel.
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Cheers Nigel but when the first bet is placed the entire formula in L9 disappears preventing me from firing more bets, I tried getting around this by pasting my formula back into the code between the quotation marks instead of just having "" but it displays a compile error, "expected end of statement". How would you get around this?

Say for example for horse 1: L9 shows =IF(A1=1,B1=1,C1=1,1,0) {Just a made up trigger instruction} but then on the second horse the code is =IF(A2=1,B2=1,C2=1,1,0). Because the cells all drop one row each time can you lock in VBA with $ or do I go about this some other way? I have 6 'brands' in total i.e. 6 horses/greyhound or whatever. So I need a code that will wipe column O when placed but redisplay the same (different on each row) trigger formula immediately afterwards and what about when L9 shows back briefly but disappears before the order can be processed?

:lol: It's funny isn't it how you think of the simplest of strategies that you think will take 10 minutes to create and then there are so many other variables that appear that you didn't even think of before!

Callum :)
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

If you want to fill a range of cells 'en-masse' this will be ok, and fill all the cells with the correct formula

Code: Select all

Range("D2:D10").Formula = "=IF(AND(A2=1,B2=1,C2=1),1,0)"
If you need to step through them and only fill every other cell, this will fail because it will always reference the original cells, ie, A2,B2,C2

Code: Select all

For x = 2 To 10 Step 2

Cells(x, 4).Formula = "=IF(AND(A2=1,B2=1,C2=1),1,0)"

Next
You have to use the R1C1 notation to get the correct formulas in each cell: (Just type your formula in as normal, then switch to R1C1 and copy the formula from the formula bar into your code).

Code: Select all

For x = 2 To 10 Step 2

Cells(x, 4).FormulaR1C1 = "=IF(AND(RC[-3]=1,RC[-2]=1,RC[-1]=1),1,0)"

Next
The 'expected end of statement' probably appears because you could have something like:

Code: Select all

Range("D2").Formula = "=IF(A2=1,"Back","")"
But if you have anything in quotation marks in the formula, they themselves have to be within quotation marks (real pain)

Code: Select all

Range("D2").Formula = "=IF(A2=1,""Back"","""")"
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

what about when L9 shows back briefly but disappears before the order can be processed
If you want it to stay there you'll have to code it in, if it's formula based it might only be there for a fraction of a second before your formulas change value.

Say I've added up several different formula and the result appears in F2, so I want to put the word "Back" in the command column. (F2 could just as well contain a total figure rather than a word).

Code: Select all

Private Sub Worksheet_Calculate()

If Range("F2") = "Back" Then
Range("F2").Offset(0, -5) = "Back"
End If

End Sub
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Thank you for such a detailed reply Nigel! I'm going to look into this - as I'm quite new to coding it may take me some time aha - but I should have plenty to go off with your reply. Thank you again!
Post Reply

Return to “Bet Angel - Example spreadsheets”