Record odds movement

[Captain Sensible]

No idea what your code's doing just place it where you think it'd run best

At present the code starts at

If [A1].Value = MyMarket Then

Which just checks the market at A1 if it doesn't equal the variable in MyMarket it clears any stored data and sets us up for a new market ie clears the stored price data etc

If your code only needs to run once on a new market you'd put it in the else section, otherwise you can pretty much put it anywhere outside the IF END IF statements. Remember the code just steps thru each part so if you want it to execute after the prices have been updated place it after that code

[vanbuuts]

I had some code which would call my macro (ClearBetData) if there was "CANCELLED" in the bet ref column for line 5. I've put the following code after the else statement but it didn't fire.

Code: Select all

'Clear Bet Data
                If Range("T5").Value = "CANCELLED" Then
Application.EnableEvents = False
ClearBetData
                End If
                Application.EnableEvents = True

[Captain Sensible]

The code after else only runs once and resets stored data , it'll just run when the market loads but never again until a new market so not the best place if you want it continually monitoring the market

Put it at the top then it'll always run even before the market resets data so it'd clear any residule data that may have come over, no need to switch off events as the codes already there to do that.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As Variant
    Static switched As Variant
   
If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

                If Range("T5").Value = "CANCELLED" Then ClearBetData
               

If [A1].Value = MyMarket Then

........................etc




[vanbuuts]

Thanks CS, I'll give that a go

[Tarmac]

Hi,

Could anyone please advise the VBA code required in order for the bet ref col (T) to contain the values in col Z, but only if cell AA1 =1. The macro would continiously run as col Z would be changing all the time.

Thanks for any response

T

[Captain Sensible]

Just noticed my PM from 2pm is still waiting to be sent, try this

Code: Select all

    Private Sub Worksheet_Change(ByVal Target As Range)
     
       
    If Target.Columns.Count <> 16 Then Exit Sub
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
       
        If Worksheets("Sheet1").Range("Z1").Value = 1 And Worksheets("Sheet1").Range("Z2").Value = 2 Then
        Worksheets("Sheet1").Range("T5:T200").Value = Worksheets("Sheet1").Range("Z5:Z200").Value
        End If
       


        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Exit Sub
       

    End Sub



[vanbuuts]

Hi CS, sorry to dig up an old thread but I've lost your original file since switching laptops and the link no longer works.

Do you have this file still?

[Captain Sensible]

Afraid not, I usually delete them from that mediafire site once they've been downloaded, I'd guess all the code was in post viewtopic.php?f=5&t=9085#p46896

[vanbuuts]

Appreciate the reply, in which case - can you help identify within your code the frequency of data grab? I think it was programmed for every 5 seconds but I can't see where its specified or indeed where this could be changed?

[Captain Sensible]

Looking at the time code it's entered in AB1 so 00:00:05 would be 5 seconds

[vanbuuts]

Thanks, as its left blank would that default to 1 second? And is there anyway to specify less than 1 second in Excel?

[Captain Sensible]

If it's blank it would default to whatever refresh rate you have the market set to. If you're looking for timings less than a second it's probably best to use your excel refresh rate to dictate the times otherwise I think you'd have to import all sorts of additional modules and use wait sleep etc