Option Explicit
Sub Test2()
With ActiveSheet.[B2].Resize([B1000000].End(xlUp).Row - 1)
.Offset(, 1).Value = ValeursMélangées(.Value)
End With
End Sub
Function ValeursMélangées(ByVal ListOrg)
Dim TOrg(), TMél(), TAlé() As Long, L As Long
If TypeOf ListOrg Is Range Then TOrg = ListOrg.Value Else TOrg = ListOrg
ListeAléat TAlé, UBound(TOrg, 1)
ReDim TMél(1 To UBound(TOrg, 1), 1 To 1)
For L = 1 To UBound(TOrg, 1): TMél(L, 1) = TOrg(TAlé(L), 1): Next L
ValeursMélangées = TMél
End Function
Sub ListeAléat(TNAlé() As Long, ByVal Nombre As Long)
Dim P As Long, A As Long, J As Long
ReDim TNAlé(1 To Nombre): For P = 1 To Nombre: TNAlé(P) = P: Next P
Randomize
For P = Nombre To 2 Step -1
A = Int(Rnd * P) + 1: J = TNAlé(A): TNAlé(A) = TNAlé(P): TNAlé(P) = J
Next P
End Sub