Clearing status cells - Excel Macros

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

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

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

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: 469
Joined: Wed Apr 15, 2009 11:00 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

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!
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

It can only be one of two things really

Either you have no formulas in your worksheet (unlikely), or it's the conditions not being met that's stopping it.

Put this in

Private Sub Worksheet_Calculate()
Dim i As Integer

For i = 9 To 67 Step 2

Range("O" & i).ClearContents
Next i

End Sub

now put anything in O9 and then in another cell put a calculation: =2 will do

O9 should now be clear?
JacoboPolavieja
Posts: 91
Joined: Thu Sep 10, 2009 6:00 pm

Hi nigelk,

Thanks for taking the time helping me out with this.

I did what you recommended with the same effect... it works as expected if I hitthe "play" button from the VBA interface, but doesn't automatically behave like that without my interaction.

The condition are met as the only condition I have is O9, O11, etc having the word "FAILED" which it clears correctly when run manually...

Pretty weird thing. I'm using Excel 2010 and I've put this in a book with macros enabled. Any other idea on how to determine where this may be coming from?

Thank you. Cheers!
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

It is the sheet, not a module?
JacoboPolavieja
Posts: 91
Joined: Thu Sep 10, 2009 6:00 pm

nigelk wrote:It is the sheet, not a module?
Yep! I've put it in the exact sheet, in the Worksheet... nothing happens automatically.

I've tried to look for anything regarding automatically running macros, but it all seems normal.

Thanks a lot!
JacoboPolavieja
Posts: 91
Joined: Thu Sep 10, 2009 6:00 pm

I really don't get it...

After thinking for a while I thought it could be an issue with the security settings for macros. I went to the Excel's "Trust Center" and enabled everything (which I know is a risk). Still... all goes fine when run manually but it does nothing if I write on a cell it should automatically clear.

I hate these things! It has to be a silly one but I can't find it!

Just needed to speak out the desperation... hehe!
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Have you got a) any disable events in your routine?
b) any error trapping routines that have
not been re-set.
c) does it call any other routines?
JacoboPolavieja
Posts: 91
Joined: Thu Sep 10, 2009 6:00 pm

To summarize the exact steps of what I'm doing:
1. Create a new Excel workbook
2. Press Alt+F11 to enter VBA.
3. Right click on the name of my sheet -> view code
4. Paste the code you put.
5. Save it.
6. Close and reopen the workbook (just in case...).
7. Write something in the fields from O9 to O17: nothing happens.
8. Enter VBA and manually run de macro: it clears out the O9 - O17 cells as expected.

Maybe I need to trigger an event for the macro to run... or should it automatically run everytime a cell is changed as I expect?

Thank you so much!
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

There are two reasons why it won't work.

It's a Calculate event not a change event, so putting anything a cell won't cause it to fire. It has to be preceded with an = sign.

Also the cells in question are formatted as text, so even if you do put a calculation in there, nothing will happen.

Put something in O9, and then in M9 put =K9, the O9 will clear.

NB. Formatting is generally for our benefit only,if you need a date who wants to see 40658 in a cell? Much better if we see 25/04/11. If a cell is formatted as text Excel will treat it as, well text, and not a number or calculation to be acted upon.
JacoboPolavieja
Posts: 91
Joined: Thu Sep 10, 2009 6:00 pm

nigelk wrote:There are two reasons why it won't work.

It's a Calculate event not a change event, so putting anything a cell won't cause it to fire. It has to be preceded with an = sign.

Also the cells in question are formatted as text, so even if you do put a calculation in there, nothing will happen.

Put something in O9, and then in M9 put =K9, the O9 will clear.

NB. Formatting is generally for our benefit only,if you need a date who wants to see 40658 in a cell? Much better if we see 25/04/11. If a cell is formatted as text Excel will treat it as, well text, and not a number or calculation to be acted upon.
Ooooooh god, it's finally working!

I didn't get that it was a calculate event only. I have the cells as "General".

My question is... will this be triggered by changes made in the sheet by BetAngel? Because the end of all this was to clear the specific cells when they showed "FAILED" or "ERROR" in order to make sure it places a bet.

Thanks a lot for your infinite patience nigelk!

P.S: I won't like to try it out with a real situation as I don't want it to start firing lots of bets without control.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

The easiest way to find out is to put this line in at the beginning or end

Msgbox "Calculation"

Then you'll see if a calculation been carried out. Obviously, take it when you are happy with what you are trying to do.
Post Reply

Return to “Tips and tricks”