VBA help needed- to copy last requested odds to another cell

[ladders]

Hi all

Firstly thanks very much to Gary for the program, and the rest of you for the help you have already given in your forum posts.

I'd like to be able to record the odds of the last order i placed on a given selection, let's say on row 5 when using an origin of A1. So, the value I want copied is in R5 (odds) at the instant the order is placed.

I'd like the value copied to Z5. This number forms part of my if formulae in Q5 (trigger) / R5 (odds).

My idea was to create a macro that would copy the value in R5 and paste as a value to Z5 when T5 = PENDING. This macro works fine when I'm not linked to BA and manually enter PENDING, but is unreliable / doesn't often work when linked to BA. I guess that this method of recording the odds is weak because PENDING may not always appear for long enough to activate the macro?

Can anyone help with a better solution for this? or is this the best type of solution and i need to play with timing settings etc? I'm fairly confident with formulae but know very little VBA. I'd like the solution to use as little VBA as possible.

thanks
Paul

[Mitch]

It sounds like your vba is ok, but what method are you using to trigger the code to run? Is it on a timer or a worksheet event?

If you're unsure what I mean you could just post the routine in here if it helps.

[ladders]

Hi Mitch

Glad to hear from you - from what i've seen elsewhere on the forum i'm sure you can help!

I have very little VBA knowledge and pieced this together based on a few bits I found on the web for other purposes. As far as I understand, the top bit of code will start up the "PasteOddsReq" macro whenever I open the workbook. Does this then just continually run over and over periodically? or will it run when any cell is changed? Is using the appearance of PENDING the best trigger to record this data?

In addition to the code below, everything between (and including) If and End If is repeated for rows 5-10 (to perform the same task for the top six runners / outcomes.

thanks.

Sub Auto_Open()
Application.OnEntry = "PasteOddsReq"
End Sub


Sub PasteOddsReq()

If Range("T5").Value = "PENDING" Then

Range("R5").Select
Selection.Copy
Range("Z5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End If

[Mitch]

Hi, sorry it took a while to reply but I've not been around much the last few days.

Hopefully we're both going to learn something here, because I've not come across Application.OnEntry before (I only ever learn enough to do what I need to do) so I've just had a play about with it.

It only seems to work when you manually enter something in the sheet, but not when BA pastes its' data there.

I use the Worksheet_Change event handler to run my macro each time BA sends info.

You obviously know something about how to do this, but if I say something that doesn't make sense please shout.

You could get rid of the Auto_Open routine, put your PasteOddsReq routine in a module, then put the following code in the code window for the sheet you have the info put into from BA.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    PasteOddsReq
    Application.EnableEvents = True
   
End Sub


That will do the job for you. It can be made a little simpler though, so much so that it's hardly worth having a seperate routine for the PasteOddsReq.

This is how I would do it....

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
   
    For i = 5 To 10
        If Cells(i, 20) = "PENDING" Then Cells(i, 26) = Cells(i, 18)
    Next i
   
    Application.EnableEvents = True
   
End Sub


The part I've substituted for the call to the original routine looks at lines 5 to 10 in a loop, and if the 20th cell across (col T) has pending in it, puts the value in the 18th cell across (R), into the 26th (Z).

The whole thing goes in the code window for the worksheet you're using.

Like I said above, if anything doesn't make sense please ask.

[ladders]

Thanks Mitch, only had time for a brief play but that seems to be working fine. Just out of interest so I can better understand what's going on, the function of most of the code you gave seems self-explanatory but what is the purpose of the line

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

Also, not sure if this kind of thing is something you use, but do you think using the appearance of 'Pending' is a reliable trigger to record this data, ie will it always appear, and if so, always for long enough to be picked up by the macro? would running the loop over a larger number of rows (say to a max of 16) or in multiple sheets with different markets simultaneously lead to unreliability?

[Mitch]

You can paste the code in the code window for each sheet you want to, unless you have a very fast refresh and loads of sheets I can't imagine it causing a problem. You can also increase the loop to cover more runners if you want to without altering reliability.
If you want to include all runners then make the loop read "For i = 5 to [F65536].End(xlUp).Row" which will run to the last row in the sheet (as long as you don't store anything else in col.F below the runners).

I'd guess that PENDING is as reliable an indicator as anything else, I personally don't use anything like this though.

"Target" is the range that has been altered on the sheet to fire the macro, so Target.Columns.Count is how many columns wide the changed area is. With triggered betting enabled, BA pastes the selections and prices first in one lump, then each row of betting data one at a time. This means that without that line of code, with for example 10 runners in the race, that the macro would get fired 11 times for each refresh which to me is unnecessary. The line of code prevents the macro running for all the single lines that are updated i.e. it will only run once per refresh.

I hope that makes sense.

[ladders]

ok thanks for the explanation, i now understand why it was only working when connected to BA.

I've placed a few bets with it and the PENDING trigger seems to work every time. I aim to run this on quite a few markets, but a high refresh rate isnt necessary so it sounds like it'll be fine.

[Princerupert]

Hi Mitch

I have just been trying to copy your previous routine.

I am new to VBA.

I have a new spreadsheet, I go to the worksheet tab and right click to paste this code in.

However nothing happens.

I am useing excel 2000

Any help appreciated

regards

Rupert


Private Sub Worksheet_Change(ByVal Target As Range)

Cells(5, 26) = "Help"

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

MsgBox Cells(3, 17)



For i = 5 To 14
'my pending value value is in Q3
'I am trying to put f5 etc into z5
If Cells(3, 17) = "PENDING" Then Cells(i, 26) = Cells(i, 6)
Next i

Application.EnableEvents = True

End Sub

[Mitch]

I might be misunderstanding what the problem is, but cells(3,17) will never have "PENDING" in. Should that line be cells(i,17)?

[Princerupert]

Hi Mitch

it seems as if you have to log out of excel for the vba code to start to be triggered.

its all working now.

only problem left is how to deal with the time in d2 properly
I wish to convert it into seconds

Thanks

Rupert

[MarkRussell]

Hi

if you want to display hh:mm:ss as just seconds, choose a different cell and use the formula =60*60*24*D2
Make sure you change the format of the new cell to number with zero decimal places.

Hope this helps.

Regards,
Mark