PDA

View Full Version : Randomly Sort Teams Button



Retroshift
05-20-2017, 04:09 PM
Hi, I have a list with couples who participate in a tourney, but the names have to be placed randomly. Only both players of the same team stay together (e.g. Chris and Maggy).
Here is a screenshot:
19238
I created a button to randomize, so that for example Mark and Steph become Couple T instead of Couple B. Could anyone help me out with the macro code of a shuffle button? Be aware that some cells are merged.
Many thanks in advance!

SamT
05-20-2017, 04:55 PM
First, You are not limited to one sheet.

Second, after you get this step done, what is the next step? And after that?

Finally, Surely you can figure out, using more than one sheet, how to lay out the Data so that you don't need to merge cells.

Logit
05-20-2017, 06:18 PM
.
.
Here is a slightly different version that can be utilized for a season of games and/or just one tournament game:



Sub Randomize()
Dim rng As Range
Dim num_rows As Integer
Dim num_cols As Integer
Dim temp() As Object
Dim row As Integer
Dim col As Integer
Dim swap_row As Integer
Dim temp_object As String


' Get the selected range.
Set rng = Application.Selection
num_rows = rng.Rows.Count
num_cols = rng.Columns.Count

If ((num_rows < 2) Or (num_cols < 1)) Then
MsgBox "You must select at least 2 rows and 1 column."
Exit Sub
End If

' Randomize.
For row = 1 To num_rows - 1
' Pick the row to swap with this row.
swap_row = row + CInt(Int((num_rows - row + 1) * Rnd()))

' If MsgBox("Swapping " & row & " with " & swap_row & ". Continue?", vbYesNo) = vbNo Then Exit Sub

' Swap the rows.
If (row <> swap_row) Then
For col = 1 To num_cols
temp_object = rng(row, col)
rng(row, col) = rng(swap_row, col)
rng(swap_row, col) = temp_object
Next col
End If
Next row
End Sub

mdmackillop
05-21-2017, 05:03 AM
Just put a random number against each pair =RAND() and sort on this column.