Tracking change in odds

[runtime_error]

I want to show in Excel the change in back odds (Column F) over time expressed as a percentage. I can do it manually by copying the back odds in F column, pasting to another column and comparing them with a formula. I have been trying to find a way (without success) to be able to do this automatically say from 120 seconds before event start. I've written VBA to copy F column at 120 seconds to another column so it can be compared to the updated odds in F. However, Excel seems to have an issue with pasting data into cells when that data is linked to formulas in other cells, although it will paste the data when it isn't linked to formulas in other cells. Anyone have any suggestions to fix this or an alternate method to track back odds changes expressed as a percentage? Thanks in advance.

[runtime_error]

To clarify, this code to copy the back odds in column F will work when it's called at a specified time before start, if no cells contain formulas dependent on the pasted data:

Sub CopyCells()
Dim sourceRange As Range
Dim targetRange As Range

' Set the source range (F5:F23)
Set sourceRange = Range("F5:F23")

' Set the target range (CD5:CD23)
Set targetRange = Range("CD5")

' Copy the values from the source range to the target range
sourceRange.Copy Destination:=targetRange
End Sub

However when there is a formula in the column next to column CD to calculate the percentage difference i.e. =(CD5 - F1) / F1 * 100) "Error 28: out of stack space" returns and Excel crashes.

[alrodopial]

the change in back odds over time as %
should be =(CD5-F5)/CD5*100
I don't know about the error

[Captain Sensible]

How are you triggering the sub routine? Seems like it might be caught up in an endless loop and running out of memory.

[runtime_error]

Thanks for the correction with the formula Captain.

I use a spreadsheet I downloaded from this site at Sample Triggered Betting Excel Sheets called "Back 3rd Favourite" :

http://www.gruss-software.co.uk/forum/viewtopic.php?f=8&t=4886

Cell S1 contains the number of seconds until start and this is used to trigger CopyCells():

Private Sub Worksheet_Calculate()
If Range("S1").Value = 120 Then
Call CopyCells
End If

I have suspected this could be problematic.

[NorthView]

It's better to copy the data into an array and work on it there.

There are some examples on this forum if you're able to search for them.

[Captain Sensible]

Thanks for the correction with the formula Captain.

I use a spreadsheet I downloaded from this site at Sample Triggered Betting Excel Sheets called "Back 3rd Favourite" :

http://www.gruss-software.co.uk/forum/viewtopic.php?f=8&t=4886

Cell S1 contains the number of seconds until start and this is used to trigger CopyCells():

Private Sub Worksheet_Calculate()
If Range("S1").Value = 120 Then
Call CopyCells
End If

I have suspected this could be problematic.

Yes, you need to ensure the code only runs once per refresh, adding additional code like this should stop that, obviously whilst S1 =120 it'll still fire on each refresh and your refresh rate has to be running fast enough to catch 120. Sometimes it's better to have <=120 and set a flag so it doesn't re-fire.

Maybe try

Code: Select all


Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As String

If Target.Columns.Count <> 16 Then Exit Sub  'If columns changed <> 16 then exit sub
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
Application.Calculation = xlCalculationManual

With Target.Parent

If Range("S1").Value <= 120 And .Range("A1").Value <> MyMarket Then
Call CopyCells
MyMarket = .Range("A1").Value
End If



Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True 'Turn on events again
End Sub


Any bits you don't understand just ask.

[Captain Sensible]

Should be

Code: Select all

    Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As String

    If Target.Columns.Count <> 16 Then Exit Sub  'If columns changed <> 16 then exit sub
    Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
    Application.Calculation = xlCalculationManual

    With Target.Parent

    If .Range("S1").Value <= 120 And .Range("A1").Value <> MyMarket Then
    Call CopyCells
    MyMarket = .Range("A1").Value
    End If



    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'Turn on events again
    End Sub




Range("S1").Value should have been .Range("S1").Value

Haven't tested it so set at small stakes to check it works for you first

[runtime_error]

Thanks Captain, just needed to add a "End With" after "End If" and it works!

[runtime_error]

It's better to copy the data into an array and work on it there.

There are some examples on this forum if you're able to search for them.

Thanks for the suggestion. Got it sorted with Captain's code.

[Captain Sensible]

Thanks Captain, just needed to add a "End With" after "End If" and it works!

Glad to see you got it working,