melanger une liste de noms

Private Sub InitAléa(TA() As Long, ByVal Nombre As Long)
Dim P1 As Long, P2 As Long, A As Long
ReDim TA(1 To Nombre)
For P1 = 1 To Nombre: TA(P1) = P1: Next P1
Randomize
For P1 = Nombre To 2 Step -1
  P2 = Int(Rnd * P1) + 1
  A = TA(P2): TA(P2) = TA(P1): TA(P1) = A
  Next P1
End Sub

Option Explicit

Sub ProduireNomAléat()
GénérerNomAléat ActiveSheet.[C3:C14], ActiveSheet.[B3:B14]
End Sub

Sub GénérerNomAléat(ByVal PlgRésu As Range, ByVal PlgNoms As Range)
Dim TNoms(), TA() As Long, TRésu(), L As Long
TNoms = PlgNoms.Value
InitAléa TA, UBound(TNoms, 1)
ReDim TRésu(1 To PlgRésu.Rows.Count, 1 To 1)
For L = 1 To UBound(TRésu)
  If L > UBound(TA) Then Exit For
  TRésu(L, 1) = TNoms(TA(L), 1): Next L
PlgRésu.Value = TRésu
End Sub

Private Sub InitAléa(TA() As Long, ByVal Nombre As Long)
Dim P1 As Long, P2 As Long, A As Long
ReDim TA(1 To Nombre)
For P1 = 1 To Nombre: TA(P1) = P1: Next P1
Randomize
For P1 = Nombre To 2 Step -1
  P2 = Int(Rnd * P1) + 1
  A = TA(P2): TA(P2) = TA(P1): TA(P1) = A
  Next P1
End Sub