Clear back trigger after off

[siony1974]

I have built my sheet and am in the process of testing it.

I am manually entering the BACK trigger to fire the bets for now and it works fine. I have the sheet set to move onto the next race at the actual off.

Once the sheet updates to the next race the back trigger is still there along with all the other info. How do I clear it ready for the next race?

The actual trigger formula is on the right hand side of the sheet (column AC). The trigger column just has =AC5 so it fills the cell and triggers the bet.

I obviously don't want anything that will delete the underlying formula. I need to keep the stake column too as that tracks the horses using the RANK function so I don't want to upset that either.

Is it a trigger modifier, an update or a type of clear trigger that I need?

I would prefer excel formulas to VBA. I'm okay adding code to modules or basic editing but anything else is an alien language to me at the moment.

I'm also noticing that in the results sheet the bet is sometimes showing as RESULT_NOT_AVAILABLE even though my balance has been updated to show the win, sometimes a good 10 minutes after the race. Any way to speed this up or I fear I will be missing races waiting for the result to come through. My staking plan is built into the results sheet and I need the Win or lose confirmation for it to do the correct calculations for the next stake.

Any and all advice you could give me would be very much appreciated.

[siony1974]

I'll happily take the VBA option though if it does the job and doesn't wipe the formula I have in the trigger column.

I just need to keep the odds and stake columns intact as well if I can.

If someone could point me in the right direction I'd be most grateful.

[Captain Sensible]

VBA is probably the easiest option just use something like this, basically it stores the market in a variable and if that changes it fires off the routine, so moving to a new market would kick it off

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value

'ENTER ALL YOUR ROUTINES IN HERE


Range("AC5").Value ="WHATEVER YOU WANT IT TO SHOW WHEN THE MARKET CHANGES"

End If
Xit:
Application.Calculation = xlCalculationAutomatic
End Sub

[siony1974]

Thank you Captain Sensible for the help.

I am thicker than a whale omelette when it comes to VBA. I reckon though, that is exactly what I'm looking for. I'm a little hazy as to what 'routines' are? Could you expand on that a little for me? I'm eager to learn.

If this removes the BACK trigger a soon as the market changes and leaves the formulas in cells in the AC column intact then that will be perfect. I assume if I want it to work on more than one cell I just use AC5:AC15 then? I would just want the cell to show as blank so "" for that part.

I take it once the code is right I can just open up the VBA editor and put the code it in the right sheet for that workbook and it will do what it needs to do.

[siony1974]

Or for the

Range("AC5").Value ="WHATEVER YOU WANT IT TO SHOW WHEN THE MARKET CHANGES"

do I put the formula that I want to appear in AC5 after the code has run? i.e the one that was there before the code runs.

[Captain Sensible]

Yep just press Alt+F11 and paste the code into the relevant sheet, I just added the 'ENTER ALL YOUR ROUTINES IN HERE bit in case you wanted to reset any other variables or cell values.

Just use

Range("AC5").Value =""

to set the cell to nothing or

Range("AC5:AC15").Value =""

if you want to reset a range to nothing

It's always worth testing codes on blank sheets first without triggers just to check it does what you need it to before using it live

[Captain Sensible]

You must have posted the second bit after I was typing.

I thought you were just sticking BACK in the cell AC5 ???

I can't see any reason why you'd want to replace it with a formula that was already there as surely the formula would just work with the new data on the sheet anyway?

[siony1974]

Like I said, I don't have a clue when it comes to this stuff. Dumb questions are bound to follow.

Many thanks for all your help Captain, very much appreciated.