Macro help (Yep, another one!!!)

[thunderfoot]

I'm using UK Greyhound markets only, so only 6 possible selections, and I make bets on 2 selections per race. The bets are 'triggered' (using formula) when certain parameters have been meet.

I have a Worksheet macro that updates 2 columns (High Odds & Low Odds) for the BACK market on each selection using the Private Sub Worksheet_Change(ByVal Target As Range) macro below, and this works fine, BUT I want to record my bet trades in a record table when both bets have been fully Matched, and this is where I'm coming unstuck.

My thoughts were to run the 'Update' macro within the Worksheet Macro .......

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
        '****************** TRACKING BACK ODDS ************************************
    '******** High BACK Odds ***********
   Doing stuff to record the BACK Odds Highs & Lows

  Update
End Sub


Code: Select all

Sub Update()
    Application.Goto Reference:="Data_Line"
    Selection.Copy
    Sheets("BF Prices").Select
    Sheets("BF Prices").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
    'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
   
    ' *********************** Sort *****************
    Application.Goto Reference:="Event_Table_Data"
    Selection.Sort Key1:=Range("Timestamp"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub


.... where the range 'Data_Line' is just a line of data containing Selection, Trap No., Odds, Stake, Time etc. etc. using a cell reference formulae for each requirement.

But what happens is I get a multitude of entries of the 'Data_Line' entry in my record table, and I only want 1 line. I know this probably isn't a new question and has been asked before (and probably answered by GeorgeUK/Mitch/Capatin Sensible or one of the other regulars), but what am I doing wrong?

Can't see the wood for the trees at the moment!!!

[dermag]

Hi Thunderfoot,

It's difficult to know the frequency of your code from the above example, so is difficult to pinpoint your problem. I don't deal in dogs at all, just horses but I'm assuming BA fires them into excel in the same fashion. IE one block of values for the 16 columns and X number of rows, then updates each row of bet data individually. So I'm assuming you will get 7 updates by BA for a dog race, IE the data block then 6 times the individual dog bet data (if any).

Are you getting 6 times the recording of the "Data_Line" stuff? or more than 6 for each bet update?

* edit1 Before I discovered an invaluable piece of code supplied by Mitch I was struggling with this sort of problem. The code is

If Target.Columns.Count <> 16 Then Exit Sub

This stops the code going berserk and may help you as it should enable you to fire your 'Update' macro only after the whole 6 bet info data has been updated, and not multiple times in between.

**edit2 Just remembered, you could also do with the line application.enableevents = false just after the target.columns.count line then the code wont try to call itself accidentally, which can be a total nightmare.

Also I think all your selecting/copying/pasteing can be swapped for a simple line like this...

Range("Data_Line").Copy Destination:=Sheets("BF Prices").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

This does the same job without having to select or paste anything

[GeorgeUK]

I think dermag has spotted the problem.

When worksheet_change is called, the data for betfair odds is updated, then each line of each selection is looked at.

If you want the update to run once after each refresh, i'd put target = countdownclock, and if target <> countdownclock then exit so you don't get multiple updates for a single update event.

Hope that makes sense.

[Mitch]

The main thoughts I have are already covered in the above posts. I have a couple of more obscure thoughts if that doesn't solve the problem but I'd need a little more detail.

[thunderfoot]

If Target.Columns.Count <> 16 Then Exit Sub

Gents,

Thanks for the feedback. Where in the coding would the above code fit?

What I'm trying to do is to get 1 line of information, AND only 1 line, into a table below where BA puts it's data in EXCEL. I want this to trigger when:
A) cell F5 = "Suspended"
B) cells in B5:G10 total 0
c) cell A1 does not equal the 1st record in the table, in my case cell A24 (the table gets sorted to bring the latest entry to the top). I use this as a check to make sure I don't add a duplicate entry.

[Mitch]

I use it as the first line in the Worksheet_Change event.

[dermag]

Hi Thunderfoot

This should do what you want. You will need to change the "YOUR RANGE" values to whatever you want.

Code: Select all


Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Columns.Count <> 16 Then Exit Sub
   Application.EnableEvents = False    'essential as any change can result in an endless loop and lock-up
   
        If Range("F5") = "Suspended" Then
       
             If WorksheetFunction.Sum(Range("B5:G10")) = 0 Then
             
                 If Range("A1") <> Range("A24") Then 'data table will need sorting so as to not duplicate
                     Range("YOUR RANGE").Copy Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                 End If
                 
             End If
             
         End If
   

    Application.EnableEvents = True

End Sub


[thunderfoot]

Hi Thunderfoot

This should do what you want. You will need to change the "YOUR RANGE" values to whatever you want.

Code: Select all


Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Columns.Count <> 16 Then Exit Sub
   Application.EnableEvents = False    'essential as any change can result in an endless loop and lock-up
   
        If Range("F5") = "Suspended" Then
       
             If WorksheetFunction.Sum(Range("B5:G10")) = 0 Then
             
                 If Range("A1") <> Range("A24") Then 'data table will need sorting so as to not duplicate
                     Range("YOUR RANGE").Copy Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                 End If
                 
             End If
             
         End If
   

    Application.EnableEvents = True

End Sub


What does this part of the above code do?

Code: Select all

If Target.Columns.Count <> 16 Then Exit Sub

I've attached the code to a worksheet but it doesn't work and I suspect it might have something to do with this part of the code. I have a number of additional entries in columns to the right of the EXCEL data downloaded by BA (i.e. in Cols Y to AS).

[dermag]

Hi Thunderfoot,

That line "If Target.Columns.Count <> 16 Then Exit Sub" means the code will only run once on every refresh from BA. BA sends it's data to Excel in one block of 16 columns (for the API version), this is usually when you want any code to run. His line stops the code running if there is a 'non BA' change to a cell anywhere.

It really is an essential line, or something like it, otherwise you can, and often do have the code call itself and spiral into an endless loop resulting in excel freezing and potential loss of data.

[Ian]

Target tells you the cells that caused the event to trigger, so I think it's skipping the rest of the code unless exactly 16 columns have been changed.

[thunderfoot]

Thanks dermag,

Right, now big back yer lug 'oles.

These are the codings I have, attached to a Worksheet and a Module.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Columns.Count <> 16 Then Exit Sub
   Application.EnableEvents = False
        If Range("F5") = "Suspended" Then
             If WorksheetFunction.Sum(Range("B5:G10")) = 0 Then
                 If Range("A1") <> Range("A24") Then
                     Call Update 'Module Macro only invoked when ALL 3 criteria are TRUE
                 End If
             End If
         End If
    Application.EnableEvents = True
End Sub


Code: Select all

Sub Update()
    Application.Goto Reference:="Data_Line"
    Selection.Copy
    Application.Goto Reference:="Data_Line_Dummy"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ' *********************** Sort *****************
    Application.Goto Reference:="Event_Table_Data"
    Selection.Sort Key1:=Range("Timestamp"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

Any reason why this shouldn't work? I've been testing the macros by manually changing the name in cell A1 (i.e. I added another letter to the end, so that A1 did not equal A24) so it should have invoked the Copy/Paste/Sort routine named 'Update' .... but it didn't!!!
I thought I would try another alternative by having a 'Trigger' cell containing an IF formula that switched to show a '1' when the A1<>A24, B5:G10=0 and F5="Suspended", but I couldn't get that alternative to work either!!


HEEEEEELLLLLLPPP!!!

[thunderfoot]

Right, now big back yer lug 'oles.

Damn the lack of editting ... pin back yer lug 'oles!!

[Mitch]

The macro will only fully run when the range "Target" is 16 columns wide, so if you don't have BA logging to Excel you need to either comment out that line or select a range 16 columns wide with no data in it and press delete (or at least that's what I do).

If you are logging from BA and it still isn't working then I'll look harder

[GeorgeUK]

Are you using the F9 break option when testing your code?

I use this when tring to break the code after a certain series of events fires, so no bets are placed and i can then step through the code with F8. I think Mitch is right about the code waiting for the 116 columns to be updated at the same time before going with the rest of the code. Does the break option stop the code when you're running this with BA updating?

[Ian]

Are you using the API version ? - the screen scaper version wouldn't have so many columns - it would be only 13 cols.