Sub Combinaisons()
Dim t#, plage, d&, c&, i&, j&, n&, tot&, tablo$(1 To 65000, 1 To 2) 'dimension à adapter
With Sheets("Combinaisons")
With .DrawingObjects("Bouton 1")
.Text = "Patientez..."
.Font.ColorIndex = 3
.Font.FontStyle = "Gras italique"
End With
.Range("B3", Cells(.Rows.Count, .Columns.Count)).ClearContents
t = Timer
[COLOR="Red"] plage = Application.Transpose(.Range("A3", .Cells(.Rows.Count, 1).End(xlUp)))[/COLOR]
d = UBound(plage)
c = Application.Combin(d, 2)
For i = 1 To d - 1
For j = i + 1 To d
n = n + 1: tablo(n, 1) = plage(i): tablo(n, 2) = plage(j)
If n = 65000 Or tot + n = c Then
.Cells(3, .Columns.Count).End(xlToLeft).Offset(, 1).Resize(n, 2) = tablo 'décharge le tableau
tot = tot + n: n = 0: Erase tablo 'réinitialise
End If
Next
Next
MsgBox Chr(10) & Format(c, "# ##0") & " combinaisons" & Chr(10) & Chr(10) & _
"Traitement : " & Format(Timer - t, "0.00") & " s" & Chr(10)
With .DrawingObjects("Bouton 1")
.Text = "Combinaisons"
.Font.ColorIndex = 5
.Font.FontStyle = "Gras"
End With
End With
End Sub