Removing "Cancelled" from Excel with kill or fill

[crocogotter]

Sometimes, when using an excel spreadsheet to trigger my bets, the bet isn't matched. What I'd like to do is use "Kill or Fill" to cancel the original bet and, as long as the betting criteria are still met, re-submit a bet at different prices. However, "Kill or Fill" generates "Cancelled" in the bet reference cell, so no further bets are generated for the selection.

Is there a way around this?

[mak]

yes
you can add in your code an = if(T5="CANCELLED","CLEAR","whatever")

[crocogotter]

I didn't think I could put anything into cells that BA writes into, which this cell obviously is. Thanks, will give it a try.

[crocogotter]

yes
you can add in your code an = if(T5="CANCELLED","CLEAR","whatever")

Struggling a bit with this, am I going to have to use programming or a macro or something, rather than simple logic equations?

[mak]

you probably have in q column eg q5 cell something like
=if(x=10,"BACK-F5,"wait")
you need to replace it with

= if(T5="CANCELLED","CLEAR",if(x=10,"BACK-F5,"wait"))

or tell me how to you trigger the bets
what do you have in q5 cell?

[crocogotter]

Q5 is either blank (ie "") or says LAY-F5 OR BACK-F5 as the spreadsheet can generate both back and lay bets; this is arrived at by a series of "IF" functions. When I tried as you suggested, I simply end up with the word "Clear" in Q5, with "CANCELLED" still in T5.

[mak]

if you don't mind post your trigger to fix it
also in your example you have "Clear" you should have "CLEAR" if that helps
if you can't post your trigger then you should test it and see the logic of if statements and place accordingly the suggestion

[Captain Sensible]

I used to use this snippet of VBA Gary posted on the forum to clear the CANCELLED bet ref's so I could resubmit any unmatched fill/kills. Worked fine for me

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
With Range("t5:t54")
Set c = .Find("CANCELLED", LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = ""
Set c = .FindNext(c)
If Not c Is Nothing Then
If c.Address = firstaddress Then c = Nothing
End If
Loop While Not c Is Nothing
End If
End With
Application.EnableEvents = True
End If
End Sub

[crocogotter]

mak

Sorry, it does say "CLEAR", so it's still not working.

Captain S

Umm, I was afraid it was going to require some VBA to get this working, the problem is I really have no idea what I'm doing with it, eg how to even set up a macro (is it?), or to get it to run on the right sheet, or anything. I've been dabbling this afternoon, but have got absolutely nowhere, I've managed to add a module but that's about as far as I've got. I simply don't know enough about it to change whatever needs changing to get it to work on the right worksheet, let alone anything else .

[Captain Sensible]

Fair enough, VBA can be daunting to start with but if you can code formulas it's not much of a step up to be honest and saves a lot of time.

All you need to do is pres Alt and F11 together to bring up the editor . You'll6 see your sheets listed double click which ever one you want the code to run on and then paste in that code into the window that comes up (not in a module) close the window and that's it.

[Captain Sensible]

Plenty of ways to skin a cat though and if you're more confident using CLEAR no reason not to go that route other than sometimes the formulas can get a bot long and confusing.

[mak]

So, if you have CLEAR in Q5 and CANCELLED in T5 nothing happens?
Is BA connected to excel?

[crocogotter]

Thanks both for all your help.

mak

Brilliant, simple solution, when BA is connected lol, my bad. I was trying to modify a worksheet while BA was running on the unmodified version, so, no, it wasn't actually connected to the spreadsheet. All working very easily now, exactly what I was looking for.

Captain S

Many thanks for your help as well, and for going into such detail explaining it. Fortunately mak's "CLEAR" is working perfectly, so I'm sticking with what I've got lol.

So, as you're both being so helpful, any chance you could have a look at another question which I'm just about to bump?