Combining a sub Worksheet Change with Calculate?

[rinconpaul]

In my Bet Angel worksheet I have a sub routine to copy the race at a certain time and paste it. As well I want to add a routine to clear the status cells. But first I'd like to direct a criticism to Bet Angel over this whole clear status saga. As a newbie, that's read all the forum posts and peoples attempts at solving this issue, it presents as a confusing hogwash of ideas and methods, that's left me scratching my head!

Surely it is an essential tool required for anyone placing bets and yet management have buried their heads and refuse to confront the issue, leaving subscribers to sort their mess out for them! After all, it's their software. There should be an approved method to clear status cells issued by Bet Angel, as part of their manual IMHO.

Anyway, back to my dilemma, brought on by these shortcomings. Can anyone offer some advise on how to combine these two Subs?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With ThisWorkbook
If .Sheets("Bet Angel").Range("B69").Value <> 1 Then
.Sheets("Bet Angel").Range("B70").Value = 0
End If
If .Sheets("Bet Angel").Range("B69").Value = 1 And .Sheets("Bet Angel").Range("B70").Value <> 1 Then
Copy_Race
.Sheets("Bet Angel").Range("B70").Value = 1
End If
Application.EnableEvents = True
End With
End Sub
-------------------------------------------------------
Private Sub Worksheet_Calculate()
If Range("O9").Value = "PLACED" Or Range("O9") = "MARKET_SUSPENDED" Then Range(O9").ClearContents
(Repeated for each Status cell)
End Sub

[KenDoubleU]

Personally I think that Bet Angel is an excellent product and that they have provided tools to meet the needs of many users. Like yourself I use Excel. But I feel that it is up to me do do whatever coding is needed to achieve the results I am looking for.

With regard to the coding that you have shown, my approach is to have all of the clearing of the Status Cells within the "Change Event". That works for me with my old version of Excel (2003).

[nigelk]

Clearing the status cells en masse. The quickest way is

Range("O9:O200")=""

changing the 200 to whatever you think the biggest number you will ever need.

or, if you want to be precise, measure the data

Dim Lr As Integer
With Sheets("Bet Angel")
Lr = .Range("A65536").End(xlUp).Row
.Range("O9:O" & Lr) = ""
End With

There is no need to select any cells or use a for next loop.

In reply to your question about merging 2 routines.You could try the following (note here that I have stepped through each status cell as you want to check each one individually).

Private Sub Worksheet_Calculate()

Dim Lr As Integer, Cel As Range, Rng As Range

With ThisWorkbook.Sheets("Bet Angel")

Lr = .Range("A65536").End(xlUp).Row

Set Rng = .Range("O9:O" & Lr)

Application.EnableEvents = False

For Each Cel In Rng

If Cel.Value = "PLACED" Or Cel.Value = "MARKET_SUSPENDED" Then Cel.ClearContents

Next

If .Range("B69").Value <> 1 Then
.Range("B70").Value = 0
End If

If .Range("B69").Value = 1 And .Range("B70").Value <> 1 Then
Copy_Race
End If

End With
Application.EnableEvents = True

End Sub

Bearing in mind that if you want to call the routine before clearing the status cells, your if statements should go before and outside of the loop.

Cheers,Nigel.

[rinconpaul]

Thanks heaps for the reply Nigel. I've put the code in but it doesn't like the Range("A65536"). I figure that this is supposed to be a range within the worksheet, but could be wrong. This vba might as well be Russian to me as I'm only a copy and paster lol

Private Sub Worksheet_Calculate()
Dim Lr As Integer, Cel As Range, Rng As Range
With ThisWorkbook.Sheets("Bet Angel")
Lr = Range("A65536").End(x1Up).Row
Set Rng = .Range("O9:O" & Lr)
Application.EnableEvents = False
For Each Cel In Rng
If Cel.Value = "PLACED" Or Cel.Value = "MARKET_SUSPENDED" Then Cel.ClearContents
Next
If .Range("B69").Value <> 1 Then
.Range("B70").Value = 0
End If

If .Range("B69").Value = 1 And .Range("B70").Value <> 1 Then
Copy_Race
End If

End With
Application.EnableEvents = True

End Sub

[rinconpaul]

OK, found the problem, it was a typo: I typed x1Up instead of xlUp. OK it works, sort of? On the first available race I could try (a US race), there was one Lay candidate. It fired twice, at 00:00:01 and 00:00:00. Then it got stuck in a loop flickering the screen and triggering the Copy_Race macro every split second. The race started some 6 plus plus minutes later and I ended up with 282,600 rows of data pasted for the one race.

So nearly there, if you could have a look at it for me please Nigel.
Cheers,
Paul

[nigelk]

You would probably need an exit sub command somewhere so that it only fires once if the conditions are met.

As it stands,there is nothing I can see to check if your conditions have been met, (what does copy race do?), and without that, it would be hard to advise further.

Cheers,Nigel.

[rinconpaul]

Copy_Race is a macro that copies a range on the adjacent spreadsheet (that generates the qualifying bets) and pastes it to another spreadsheet as a record:

Code: Select all

Sub Copy_Race()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Price Order")
Set pasteSheet = Worksheets("Sheet1")

copySheet.Range("I1:AF48").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[nigelk]

As the status cells are continuously being cleared it will constantly trigger your code. See here for an explanation of the status cells:

http://www.betangel.com/user-guide/bet_status_cell.html

I'm assuming that a runners name must be identified somewhere in your calculations, and the attached workbook uses that to identify which status cell to clear.

The code manually puts "PLACED" into Bet Angel O9:

Sheets("Bet Angel").Cells(Rowref, 15) = "PLACED"

that line will have to be removed so that it can be populated by your own criteria


On sheet 2 my selected trade is Uncle Danny, and I have already placed a trade. The code in the worksheet takes this into consideration and will not trigger the Copy_Race sub if Range("B3")="PLACED"

There is a reset button that clears the status cell FOR THAT PARTICULAR RUNNER ONLY, so that another trade can take place.

The other button (Global Reset), will clear all the status cells.

[rinconpaul]

Nigel, you've thrown me with that last post, sorry! Mate, I have had this all working using trigger buttons and now just want to go that little bit further and fully automate it. Your 2nd last code nearly did it, just tripped up with the Copy_Race macro. If you don't mind , I've attached my workbook with your 2nd last code on board. If you have time, could you try and fix that last bug. Thanks in anticipation,
Paul

[rinconpaul]

I thought my attachment would go?

[rinconpaul]

One other thing Nigel. Your code disarms Automation rules. I have a rule for all markets to Take SP, but unmatched bets are cancelled at inplay.
Thanks,
Paul

[nigelk]

If you want to post a sheet that contains VBA, you'll have to zip it before attaching.

[rinconpaul]

Gotcha, try this

[rinconpaul]

OK, I've resigned myself to the reality of Bet Angel. You cannot AUTOMATICALLY select, place SP bets using a timer, clear status and copy data all at once via an attached excel worksheet!
You can do two out of three though, but even that has limitations i.e:
Timer triggers bets OK but continues placing bets (Rule to counter:- In each 'L' col cell add "IF(C5=0)"
It becomes a lottery after that, especially if you have more than one bet to place. Might get all bets placed, but more than likely they'll be double bets placed within one second of each other.

Can't copy data on attached worksheet at same time as trigger. (Solution: Run 2nd instance of BA, just to record)

This is the best I can come up with given the forum postings, user manual and requests for assistance. It's a pity, that to be a profitable punter nowadays, it's not just about having a winning strategy, but also being a competent computer programmer.

[nigelk]

Can't agree with you there. It's perfectly capable of doing what you've requested.

There are a couple of spreadsheets available that might point you in the right direction.

See here for a datacapture spreadsheet that has a countdown timer on the BA sheet, a timer that records data every ## seconds (set by the user), and a procedure that resets everything on a market change:

http://www.tradersandpunters.com/data-c ... readsheet/

also see here for a procedure that clears the status cells on a market change. This could be incorporated into the above sheet.

http://www.tradersandpunters.com/knowledge-base/

If you need to place a bet only once (single or multiple runners), don't clear the appropriate status cell once the bet has been placed. (Step through each cell and decide what you want to happen next depending on what it contains)

cheers,Nigel