mardi 29 mai 2018

Generate Even Amount of Runs in Left and Right Lanes for Racers

I'm working on a spreadsheet for a soap box derby type race that can automatically generate an even amount of runs in the left and right line per racer. It also will randomize who races against who. Currently, I have 6 heats and a button above each one. It pulls from a list of racers with a randomly generated number in the cell next to it using the method shown here: https://www.extendoffice.com/documents/excel/4591-excel-random-selection-no-duplicates.html

This is what the sheet looks like.
img The 'DON'T TOUCH' column is then copied to another sheet and placed in each heat when a button is pressed above that heat. The heat sheet looks like this: img

Each time a heat button is clicked, it will copy and paste from the "Randomizer" sheet and since the sheet refreshes each time, it will be randomized on each button click. The following macro runs when a heat button is clicked.

Sub btnHeat1_Click()
  On Error Resume Next
  Dim xRg As Range
  Dim WS As Worksheet
  Dim Shp As Shape
  Set xRg = Application.Selection
  Set WS = ActiveSheet
  Set Shp = WS.Shapes("btnHeat1")
  Worksheets("Randomizer").Range("E4:E62").Copy
  Worksheets("The Race is On").Range("F4:F62").PasteSpecial xlPasteValues
  xRg.Select
  Shp.Visible = False
End Sub

I need to improve the randomizer so that each racer has an even amount of runs in the left and right lane (3 times each side). I'm not sure how to go about doing this and couldn't find any examples online of a similar situation (drag race heats, golf outings, etc). I thought of recording right and left lane each time a heat button is clicked, but not sure how to implement that into the existing randomizer. Or all the heats need to be generated at once and right and left lanes can represent a 0 and 1 in the randomizer equation.

Any suggestion on how to accomplish this? Thanks!




Aucun commentaire:

Enregistrer un commentaire