VBA assign random variables to cells

[kalo7932]

Hi, I'm trying to create a macro where the user is prompted to enter team names for a softball league and then it randomly assigns the names to cells in the spreadsheet (that way they can create random teams each week). My code is set up right now so that all of the team names that have been entered are string variables. Is there a way to do this with VBA?

Thanks!

[Paul_Hossler]

I'd do something simple along these lines, assuming I'm understanding

In the attachment, you can click the arrow, but I listed the teams in Col A






Option Explicit

Sub GeneratePairings()
Dim rTeams As Range
Dim aPair() As Variant
Dim i As Long, j As Long, iOut As Long
Dim sTeam As String, dOrder As Double

Set rTeams = ActiveSheet.Cells(1, 1).CurrentRegion
Set rTeams = rTeams.Cells(2, 1).Resize(rTeams.Rows.Count - 1, 1)


ReDim aPair(1 To rTeams.Rows.Count, 1 To 2)

For i = LBound(aPair, 1) To UBound(aPair, 1)
aPair(i, 1) = rTeams.Cells(i).Value
aPair(i, 2) = Rnd
Next i


'simple bubble sort
For i = LBound(aPair, 1) To UBound(aPair, 1) - 1
For j = i To UBound(aPair, 1)
If aPair(i, 2) > aPair(j, 2) Then
sTeam = aPair(i, 1)
dOrder = aPair(i, 2)
aPair(i, 1) = aPair(j, 1)
aPair(i, 2) = aPair(j, 2)
aPair(j, 1) = sTeam
aPair(j, 2) = dOrder
End If
Next j
Next i

iOut = 2
ActiveSheet.Cells(iOut, 7).Resize(ActiveSheet.Rows.Count - 1, 1).ClearContents
For i = LBound(aPair, 1) To UBound(aPair, 1) - 1 Step 2
ActiveSheet.Cells(iOut, 7).Value = aPair(i, 1) & " vs. " & aPair(i + 1, 1)
iOut = iOut + 1
Next i
If UBound(aPair, 1) Mod 2 = 1 Then
ActiveSheet.Cells(iOut, 7).Value = aPair(UBound(aPair, 1), 1) & " Bye"
End If

End Sub