Clearing Bet References

[boycee]

Hi guys long time no see.

I want to be able to clear the bet ref column without using the CLEAR command in the trigger column. (Using it in another application)

What would the excel formula be for doing that or would I have to use a macro?

cheers.

[GeorgeUK]

You would definately need a macro to do that.

A formula can only alter the cell that it is in, not another cell.
As the BA puts in the bet ref, this would overwrite any formula in that cell, so you will need a macro to clear any betref.

[boycee]

George,

Could you give me an example of what would work for me?

[GeorgeUK]

You'll probably need to change the Range from Q to about T if using the API version - can't remember what it is .

Rightclick the sheet tab, view code and paste this in.
It will run every time a cell is changed on the sheet, but should be restricted to only one cell change each time.
(So it isn't running every time any of the cells gets updated)

If the cell in Q for betref says pending or error, it won't register, but remain until the betref says something else.
Then it gets changed to "" (a blank cell.)

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("Q5:Q50")) Is Nothing Then
    If target.value = "Pending" or Target.Value <> "Error" then goto 1
    'Target.Value = ""
    '**Range(Range("A" & target.row),Range("AC" & target.row)).value = ""
End If
1
End Sub

You may need to refine it a bit. I think if you have the odds and stake still in it will just keep betting so you probably want to change that. I have put ' infront of that dangerous line so it will not fire until it is removed. You probably want the one that has '** in front of it. This will delete any formulas you have in that row though as it deletes everything in the trigger row from column A to AC.

This is why betting for stakes of 0.02 is very handy when testing.

let me know if it helps
George

[boycee]

cheers George - will give it a go

[boycee]

Struggling a bit George to get what I'm after and it's because I'm not sure of this code language which is completely foreign to me.

Say I want to clear out any Bet Refs in the range O8:O14 whenever the value of cell $d$2 = 1

What would the code be for that simple task. Once I understand that then I can add the safety features to the code.

I have tried googling for a tutorial on this kind of stuff, but some of it is hard for a novice to understand.

[GeorgeUK]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("D2")) Is Nothing Then
    if target.value <> "1" then goto 1
    Range("O8:O14").value = ""
End If
1
End Sub

Say I want to clear out any Bet Refs in the range O8:O14 whenever the value of cell $d$2 = 1

OK lets say you wanted to clear O8:O14 every time $d$2 = 1

If Not Intersect(Target, Range("D2")) Is Nothing Then
This line is looking at D2 to see if it is the cell that has changed and triggered the code to run.
If it has changed and is not blank, it will follow the instructions beneath it.

This would also mean that "Target" referred to cell D2

If you want to find various pieces of code that may help, try going to mrexcel.com
The message board therehas alot of excel examples and once you go to the message board you can search for keywords.
So you will see different examples of how the code can be used and written.

I know i said it before, but please be very careful that your code is not going to just send bet after bet on the same thing. It can be a costly mistake.
If you want to run through the code line by line, with the code open, press F8 and this will step through each line of code. Hover your cursor over different parts of the code like in my example target.value and this will show the actual value of the target cell.
if you press F9 on a piece of code, it will highlight it brown and allow you to run the code up to that "break" point and will wait for you to allow it to continue before going beyond that point. Very handy if you only want the code to run once, but there is the possibility that it will do so many times.

[boycee]

Thanks George - getting my head round it now

Sticking with the example above. When I key in the value 1 into D2 the range O8:O14 clears ok, but when my application brings in the value 1 from betfair into D2 nothing happens. If I then key in 1 it clears the range once again. So it does not like the imported data for some reason.

Any ideas why?

[GeorgeUK]

Is D2 the countdown clock?

it is looking for the value "1" but you may be looking at "00:01:00" or something similar.
If D2 is anything other than the value "1" as in the number one, it will see it as a different value.

try putting in the line
x = target.value

then step through the code and see what X is
(hover your cursor over x )

[BlueBoy]

If Georges suggestion does not bring any joy it may be the value of target.count that is causing the problem.

When the application sets the values then target.count may not be 1 so the routine is not actioned.

You could try temporarily putting

msgbox(target.value)

before the line If Target.Count > 1 Then Exit Sub

Then if a msgbox is displayed you will see the value of target.count
If a msgbox is not displayed when the application sets the values it suggests the routine is not being actioned and you may need to use a different event

I'm no expert at this lark so I hope the above helps rather than hinders your attempts to solve the problem

Good Luck

[boycee]

It's not the clock. It looks like a 1, but obviously it aint

When I press F8 (code open) I just get a beep (error)

Should the x= target.value be antwhere in particular?

[GeorgeUK]

in the vb editor, goto Insert > Module

sub test()
x = Range("D2").value
end sub

Paste this in and step through it using F8
hover your cursor over the x before and after it has been highlighted yellow

[boycee]

says

x=range (D2).value = 1

[boycee]

BlueBoy will try your suggestion if George can't crack this, but i have faith

[GeorgeUK]

what is D2?

The code should run when BA changes D2 to something other than 1.