Retrieving all existing Markets

[gizzard]

I want to get all the race times for all of the markets in a specific Pick List.

I'm using Q2 to reload the pick list and select the first market but am having trouble using -1 to go through the markets one by one.

When I use -1 in Q2 it retrieves the next market but doesn't seem to load it fully before going on to the next market. I'm using a check on whether A1 has changed to identify whether the new market has been loaded.

Also, how can I identify when the last market has been reached so that I can use -5 in Q2 to pick the first market again?

A bit of code would be ideal or some tips on how to recognise that the new market has been fully loaded before trying to fetch the next market.
Or a pointer to another post. I couldn't find one.

Thanks

[alrodopial]

If A1=AAA1 then copy your data, copy A1 to AAA1, place -1 to Q2
If A1<>AAA1 then copy A1 to AAA1

Instead of AAA1 you can chose whatever cell you want or a variant
For the last race use a timer and set ...secs for a macro to be run

[osknows]

This example forwards the market as soon as the current market goes inplay. You can change this condition to whatever you want.
Cell J3 indicates with "L" if the market is the last in the QPL so will send a -5 to select the first market.

Sheet Object

Code: Select all

Option Explicit

Dim currentMarket As String
Dim nextracetrigger As Boolean


Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Target.Columns.Count <> 16 Then Exit Sub

    Application.EnableEvents = False

    With Target.Parent
   
        If .Range("A1").Value <> currentMarket Then
                'New Market Selected
               currentMarket = .Range("A1").Value
               nextracetrigger = False
        End If

        'forward market at start of inplay
        If .Range("E2").Value = "In Play" And nextracetrigger = False Then
            nextracetrigger = True
            If .Range("J3").Value = "L" Then
                .Range("Q2") = -5
            Else
                .Range("Q2") = -1
            End If
        End If
       
    End With
   
    Application.EnableEvents = True

End Sub

[gizzard]

Thanks for both of those fast responses. I will take a look and give it another go. I wasn't awae of the use of J3.