View Full Version : List all possible order combinations
fb7894
05-13-2017, 06:56 PM
I'm looking for a function or algorithm that will return a list of values in every possible order.
For example, say I have an array with 3 entries (A, B, C). There are 6 possible ways these values may be ordered.
ABC
BAC
ACB
CAB
CBA
BCA
Can someone point me to some VBA code that would return all 6 combinations above? Keep in mind that I could have more than 6 entries.
Thanks in advance.
I learned from gekkasuikou san
Option Explicit
Dim p() As Long
Dim s As Variant
Dim m As Long
Dim k As Long
Dim w()
Sub test_by_gekkasuikou_san()
Dim i As Long
k = 0
s = Array("A", "B", "C")
m = UBound(s) + 1
ReDim p(1 To m)
ReDim w(1 To m * (m - 1), 1 To m)
For i = 1 To m
p(i) = i - 1
Next
Perm 1
Worksheets.Add
Range("A1").Resize(k, m).Value = w
End Sub
Private Sub Perm(n As Long)
Dim i As Long
If n < m Then
For i = n To m
Swap p(n), p(i)
Perm n + 1
Swap p(n), p(i)
Next
Else
k = k + 1
For i = 1 To m
w(k, i) = s(p(i))
Next
End If
End Sub
Private Sub Swap(ByRef A As Long, ByRef B As Long)
Dim T As Long
T = A
A = B
B = T
End Sub
fb7894
05-14-2017, 06:08 AM
That works great for ABC. But when i change to Array("A", "B", "C", "D"), i get a subscript of out range error??
fb7894
05-14-2017, 06:39 AM
Update: I changed the following line of code. It is working now. Thanks for the help!
' ReDim w(1 To m * (m - 1), 1 To m)
ReDim w(1 To Application.WorksheetFunction.Fact(m), 1 To m)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.