Simple Macro/VBA code required

[doris_day]

I need to be able to CLEAR any Betref and Bettime cells at 2 specific moments in-running to allow further bets.
I have a countdown timer which I picked up from another thread on here and that works well. The timer shows the countdown in cell AB2, so when that reaches,say, '90' and '50' , I'd like the Betref and Bettime cells to CLEAR at those 2 moments.
It would be great if someone could indicate how I write this as either a Macro or VBA routine.
Thanks...

[Spike]

Depending on how your triggers work you could probably use a simple "IF" statement in the bet ref cell- but make sure you put it between two times so that the time doesn't have to match perfectly. I use a lot of "IFs" and not much VBA, in theory it's slower but personally I don't see it making much difference especially when your bets are delayed by 1 seccond anyway. Obviously it deoends what you're doing.

[doris_day]

That was my first thought, so I'll give it a test today and see what happens. Ideally I need to it be as fast as possible but I'll see how I get on.
Thanks for the suggestion...

[Spike]

The difference in speed will be thousandths of a sec it it's just a simple thing like that. If that's the only issue- don't worry! Just make sure the time interval that you clear during is long enough that it will deffinately come up (this will also depend on your refresh rate)- but not so long that you cause a problem by clearing the next set of bets when you may not want to (or guard against this some other way). You can also check that there is a bet ref in place so that your "clear" trigger disappears as soon as the bet ref is cleared.

[doris_day]

Thanks. This throws up something I havent done before. What's the Excel code for 'leave cell alone' ? For instance, if I write this: IF(AND($AB$2<91,$AB$2>89),"",'leave cell alone') what do I put in the 'leave cell alone' bit ?
Excuse my ignorance
BTW I refresh at 0.1sec

[Spike]

You can just leave that part of the function blank (it's optional), or you can put "" (which will display nothing) or you might want to put "WAITING" or similar- so that you can see what's going on.

that bit of the cell should look like this:
IF(AND($AB$2<91,$AB$2>89),"CLEAR")
or:
IF(AND($AB$2<91,$AB$2>89),"CLEAR", "")
or
IF(AND($AB$2<91,$AB$2>89),"CLEAR", "WAITING")

It all depends on how you're putting your trigger into the cell though. Personally I write the whole triggering system into the cell as one nested logical function (if I can)- if you're doing it another way though (for example using a macro to insert it) then you might have to be carefull how you go about it- you might end up with your macro wiping out your if statement.
Doing things this way is just my personal preference and suits my way of writing bots. Something else might suit you better and I'm sure others will be able to offer other suggestions.

[doris_day]

Thanks for that....I run my trigger as a fairly complicated maths routine in the normal trigger column and haven't used the 'CLEAR' function before to remove the Betref, so wouldn't quite know where to put it within the tigger itself. So perhaps its simpler to just keep it to the Betref column....

[Spike]

oh- and to add a check for a bet ref you put:

IF(AND($AB$2<91,$AB$2>89, ISNUMBER(T5)),"","WAITING")

The structure of an if statment is always:
If (Whatever you put as your tests- in this case the time and bet ref) is TRUE then do X, otherwise do Y. in the "otherwise do Y" part you can put another IF, and keep nesting them like that.
It's an easy way of witing simple bots with no VBA, although it does have its limitations.

If you struggle with long or complex logical functions I's reccomend writing them in Open Office, which alwasy struck me as easier, then import them into Excel. They drop right in.

[Spike]

Another thought- sometimes I run a whole lot of "triggering" collunmns to do particular things in specific scenarios, then I make those colums show numbers and I reference the Trigger column to those with a simple logical function. If your logic is becoming complex and you're starting to lose track that can be a good way of simplifying things.

You could do it like that, or all in one go like this:

If (Your maths is TRUE, BACK, IF(it's time to clear and there's a bet ref, "CLEAR", "WAITING"))

[doris_day]

I understand the concept of nested IFs but I wasn't that aware of the order of things within the Trigger column and what other columns it could effect...

[doris_day]

So, from what you're saying it looks like 'BACK' and 'CLEAR' are like universal commands for a particular Row and whenever they appear a bet is made for that runner. Is that right, or does 'BACK' have to be seen by Excel in the Trigger column only ?
Again, my apologies for being a little backwards..

[Spike]

Triggering commands (BACK, CLEAR etc) have to be in the trigger column. If for example you wanted to have one bot triggering in lots of different ways- maybe so that it's effectively several bots in one, or for other reasons, one way of doing it is to have another column checking for the right circumstances and displaying "1" (or whaterver) if they arise. Let's say it's on Column AA and you're on row 6. You can then just set your actual trigger collumn to display "BACK" (or whatever) if AA6 shows 1.

EG:
If(AA6=1, "BACK", "WAITING").

Maybe you also want to back if AB6 is showing 1, and if the time is right you want to clear, in which case you put:

IF(OR(AA6=1, AB6=1), "BACK", IF(it's time to clear, "CLEAR", "WAITING"))
And so on.

Like I said earlier this approach has it's limitations but the advantage is that you can write quite complex bots without any VBA.

[doris_day]

OK. Understood. That's cleared up a few things I wasn't sure about...
But I thought the IF statements you mentioned should be placed in the Betref cells ? Does that mean 'CLEAR' also works in the Betref column ?

[doris_day]

I've now added the IF/Time and 'CLEAR' statements at the end of my Trigger and I think that should work fine as it follows the logic you mentioned. And I've deleted the 'CLEAR' If statements from the Betref column.
I'll give it a try out this afternoon but I think it looks as though it should work fine...
Thanks again for your help

[Spike]

If I were you I wouldn't wait for a real market to test out a change to a bot- log any market into your sheet to get some numbers in then break the link to excel and change the numbers in the relevant cells (time etc) manually to be sure it's all going to work. And good luck....