CallumPerry wrote: ↑Wed May 02, 2018 4:37 pm
Or is there an easier, simpler and less time-consuming way of presenting everything? Can you have the same Case names (A, B, C, D etc) for different runners or do these terms have to be unique also i.e. Case "A Runner One"?
Rather than having separate variables for each runner what you normally do is stick the data into an array and then loop thru each element (i.e. runner) at a time and adjust the data each time using the same Statemachine sub routine but obviously with different cell addresses.
So a simple Loop in VBA would be something like
Code: Select all
Dim i As Long
For i = 1 To 3
MsgBox i
Next i
So the code is simply stepping through the numbers 1 to 3 and executing the code inbetween each step and in each step i holds the value from 1 to 3. We can reference that number in a cell address simply by referencing a cell like so
Code: Select all
Dim i As Long
For i = 1 To 3
Worksheets("Bet Angel").Range("A" & i).Value = i
Next i
So our code now cycles thru A1 to A3. Obviously your number of runners may be more than three so to find that last row with data we'd use the following to put the row number into a variable called LastRow, obviously if your runner data is in a different column than A change it
Code: Select all
Dim LastRow As Long
LastRow = Worksheets("Bet Angel").Cells(Rows.Count, "A").End(xlUp).Row
Still with me, putting that lot to loop thru column A from A1 to the last cell would be as follows
Code: Select all
Sub looping()
Dim LastRow As Long, i As Long
LastRow = Worksheets("Bet Angel").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
MsgBox " Cell A" & i & " Contains " & Worksheets("Bet Angel").Range("A" & i).Value
Next i
End Sub
So with your Statemachine routine you'd simply stick the routine within a loop and reference cells in relation to your varaiable held in i, i.e.
StateCellOne = Worksheets("Bet Angel").Range("A" & i).Value 'set the variable
ActionCellOneVal = Worksheets("Bet Angel").Range("A" & 1 + i ).Value 'set the variable
Might be a bit to get your head around looping thru arrays so start off simply just to undersatnd how they work