Hi, I am a very beginner in excel vba. I need to generate combinations with
repetition.
If definition is required, the site below will be the reference. SITE
For code below,
CCCAAMMM
MMAMCCCA
etc
will be generated.
However, the VBA combination generator that I want to develop should only
produce just ONE of the many repeated combinations (CCCAAMMM,MMAMCCCA,etc).
Well, to define in a better way.
Say, building up combinations of 4 alphabets with {A,B,C}.
Repetition of available alphabets (ABC) is allowed so that:
ABCA, ABBC,AAAA...
such combinations are possible.
Repetition of combinations with similar element/alphabet is not allowed, so
that:
ABCA, BCAA, AABC..... (repetition is considered to have occurred)
there are many of them, but in my case, I only need one, anyone of them,
because
they will produce similar result in the later stage when i sum up values from
each of them.
The reasons of doing this is to reduce the possible combination and increase
efficiency of analysis.
Code:
Sub allup()
Dim a, n As Integer, c(), k As Long
Dim u1 As Integer, u2 As Integer, u3 As Integer
Dim u4 As Integer, u5 As Integer, u6 As Integer
Dim u7 As Integer, u8 As Integer, u9 As Integer
a = Array("C", "M", "U")
n = UBound(a) + 1
ReDim c(1 To Rows.Count, 1 To 9)
For u1 = 1 To n
For u2 = 1 To n
For u3 = 1 To n
For u4 = 1 To n
For u5 = 1 To n
For u6 = 1 To n
For u7 = 1 To n
For u8 = 1 To n
For u9 = 1 To n
k = k + 1
c(k, 9) = a(u9 - 1)
c(k, 8) = a(u8 - 1)
c(k, 7) = a(u7 - 1)
c(k, 6) = a(u6 - 1)
c(k, 5) = a(u5 - 1)
c(k, 4) = a(u4 - 1)
c(k, 3) = a(u3 - 1)
c(k, 2) = a(u2 - 1)
c(k, 1) = a(u1 - 1)
Next u9, u8, u7, u6, u5, u4, u3, u2, u1
Cells(1).Resize(k, 9) = c
End Sub
Please kindly enlighten.
Thanks.