vba help

[Captain Sensible]

Haven't had time to look at your myQ2 code so not sure what you're trying to do with it to be honest, I'd imagine it's skipping races because it's maybe not clearing variables when you enter a new market. You should also consider looking at clearing cells when you enter a market too as I can't see any code to do that either. Probably a bit of a blessing it's not working because it'd be placing bets as soon as it enters a markets at the moment.

[Captain Sensible]

Here's some code for you to play around with to switch between markets, it's basically the same code I use so I know it works.

It's easier for me to just copy and paste something I use rather than try and unravel why your MyQ2 code isn''t working tbh. Just try and understand what it's doing before throwing other coding into it, there's also plenty of bits you can take out later where it's just echoing the status of any variables to the screen whilst you try to understand it. It needs the Function LastRow in a module for it to work but otherwise shoud be fine, just add the reset macro into the module also so it's ready for any amendments.

Code: Select all

Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As String, Streaming As String, exitingMarket As String
    Dim rowFindLast As Integer
       
   
        If Target.Columns.Count <> 16 Then Exit Sub
        Application.EnableEvents = False


        With Target.Parent
       
                'Let's display the status whilst developing''
        .Range("S1").Value = "Your code has stopped"
        .Range("S2").Value = "Exciting market : " & exitingMarket
        .Range("S3").Value = "Streaming is " & Streaming
        '''''Just delete or comment out when no longer needed '''''
     
        ' Let's set up the new market
         If .Range("A1").Value <> MyMarket Then GoTo marketSetup
         ' Let's set up the new market
         
         ' simple routine to move between markets
         If (.Range("E2").Value = "In Play" And .Range("F2").Value <> "") Or .Range("F2").Value = "Closed" Then
         If exitingMarket = "NO" Then .Range("Q2").Value = -1
         exitingMarket = "YES"
         GoTo xit: ' no point running thru the rest of our code
         ElseIf .Range("E2").Value = "In Play" Then
         If Streaming <> "ON" Then Streaming = "ON": .Range("Q2").Value = "FULL-STREAM-ON"
         Else
         If Streaming <> "OFF" Then Streaming = "OFF": .Range("Q2").Value = "FULL-STREAM-OFF"
         End If
         ' simple routine to move between markets
         
       
         
         
         'your main code routines can go in here
         
         
         
         
         
         
         
         
         
         
         

       
       
       
       
xit:

        Application.EnableEvents = True
        .Range("S1").Value = "Enable Events is " & Application.EnableEvents
            End With
        Exit Sub
       
       
marketSetup: 'let's cleanup from the previous market and reset any variables
 With Target.Parent
        MyMarket = .Range("A1").Value
        exitingMarket = "NO"
        Streaming = ""
        rowFindLast = LastRow(.Range("Q:U"), 0)
        .Range("Q2,Q5:U" & rowFindLast).Value = ""

       

        Application.EnableEvents = True
        .Range("S1").Value = "Enable Events is " & Application.EnableEvents
        End With
    End Sub


[mak]

Thanks a lot Captain!