Bet Angel - Example spreadsheets : Clearing status cells - Excel Macros

Example spreadsheets and comments on example spreadsheets.
Nero Tulip
Posts: 411
Joined: Wed Apr 15, 2009 5:29 pm

Thu Apr 16, 2009 6:14 pm

PeterLe wrote:Thanks Guys
Really appreciate your help with this...
I'm not an Excel wizard, but I'm going to have a go. (Think I'll transfer all the funds except say £20, into the Aus Wallet Just in case!!! )
Regards
Peter
Better to be safe to begin with. The BA chaps leave it this way for a reason - to make sure it's safe to use. This macro is dangerous if you don't have the right money management stuff in the rest of your sheet.

Also, this will only clear the status cell relating to selection one in the market. (In case that wasn't obvious! apologies) To do the others you have to copy and paste the same and rename the cells for each row.

User avatar
TheTub
Posts: 267
Joined: Thu Mar 26, 2009 7:53 pm
Location: Nottinghamshire

Thu Apr 16, 2009 8:55 pm

Yup that works.

I tried this method in the past and couldn't get it working. Must have made a simple error.

Thanks for the code.

nigelk
Posts: 444
Joined: Wed Apr 15, 2009 11:00 pm

Sun Apr 19, 2009 12:11 am

This link might give others help if they are looking for something similar.

http://www.ozgrid.com/VBA/run-macros-change.htm

PeterLe
Posts: 3340
Joined: Wed Apr 15, 2009 3:19 pm

Fri Apr 24, 2009 11:42 pm

Hi Nero
Been experimenting with this...and at the risk of being a pain..can I ask for more advice?? ! (",)
Following on from the user "BetAngel" post...where they state that the excel refresh state is slower when having many worksheets open.. I really just want to have the single worksheet file open..(using Guardian to switch markets). Within the worksheet I have created a macro called "Clear_Status"

I can get your code working... but rather than it constantly clearing the contents.. I only want to run the code Just once, Ideally right at the end of the race when it displays "Suspended"..

So I can either use your code..(modified in some way) Or I believe that it is possible to call a Macro from within VBA when a cell displays a certain string..?

If you are able to help..could you explain how I do it in easy steps??! (Still getting the hang of Macros and VBA)

Thank you
regards
Peter

hmw
Posts: 7
Joined: Wed Apr 15, 2009 9:58 pm

Sat Apr 25, 2009 7:00 am

This is the code I use to reset the status everytime a bet has been placed; it's not a macro, it's code triggered by the worksheet_calculate event:

Code: Select all

Private Sub Worksheet_Calculate()
Dim i As Integer
For i = 9 To 67 Step 2
    If Range("O" & i).Value = "PLACED" Then _
    Range("O" & i).ClearContents
Next i
End Sub
And you could simply wrap this in an IF statement...

Code: Select all

If Range("H1").Value = "Suspended" Then
    For i = 9 To 67 Step 2
        If Range("O" & i).Value = "PLACED" Then _
        Range("O" & i).ClearContents
    Next i
End If
The code should be in the module described below by Nero Tulip » Thu Apr 16, 2009 5:44 pm

PeterLe
Posts: 3340
Joined: Wed Apr 15, 2009 3:19 pm

Mon Apr 27, 2009 9:17 am

Thanks HMW
That looks a good way of doing it.
I'll have a play around with it.
I have decided to learn VBA...but a Dummys guide yesterday and another book on its way from Amazon..! I certainly have the incentive to learn VBA...and its surprising how quickly you can learn stuff when you need to!
In the meantime, I may ask some very basic questions, but they will get less as time goes by..I am a great believer that you get out of something what you put in...
Thanks for bearing with me!
Regards
Peter

dogform
Posts: 53
Joined: Fri Sep 11, 2009 12:27 pm

Thu Sep 17, 2009 3:21 pm

I am using the code pasted below and originally kindly posted by "hmw" to clear the status field when the market is indicated as suspended via cell H1.


Private Sub Worksheet_Calculate()
Dim i As Integer
If Range("H1").Value = "Suspended" Then
For i = 9 To 67 Step 2
If Range("O" & i).Value = "PLACED" Then _
Range("O" & i).ClearContents
Next i
End If
End Sub


I would like to be able to clear the status field (column "O") for other circumstances. For example, I wish to place a back bet with a tick offset incorporating a fill or kill and stop loss. If I understand the process correctly, the status field, "O", will show possible values of PENDING, PLACED, CANCELLED.

I wish to be able to automatically clear the status field "O" if the values are PLACED or CANCELLED or any other values except PENDING and the market has not been suspended (H1)and is not In Play (G1). This should allow repeated firing of the bet trigger unless it gets to the point where my other criteria controlling the arming and firing of the trigger stop it firing.

Any help with the code greatly appreciated as my VBA skills are pretty much non existent.

Thx & Rgds
Dog

JacoboPolavieja
Posts: 91
Joined: Thu Sep 10, 2009 6:00 pm

Fri Apr 22, 2011 11:44 am

This is embarrasing but after having searched for a while I can't get it working...

I copy hmw's code into a new module. The problem is it doesn't automatically run. If from the VBA screen I run it it works ok. If I don't do that manually it doesn't clear the cells... What am I doing wrong? How do I enable the macro to always run?

Thanks! Cheers!

nigelk
Posts: 444
Joined: Wed Apr 15, 2009 11:00 pm

Fri Apr 22, 2011 12:35 pm

The code needs to go in a worksheet, not a module.

Select the sheet tab and right click. Select "view code"
and pop it in there.

Nigel.

JacoboPolavieja
Posts: 91
Joined: Thu Sep 10, 2009 6:00 pm

Sat Apr 23, 2011 12:01 pm

Hi nigelk and thanks for helping.

I've done what you said and it remains the same... no automatic action but works well when manually run :roll:.

Any other ideas? Thanks!

Post Reply

Return to “Bet Angel - Example spreadsheets”

  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest