| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination and Repetition Not Allowed
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 Thanks. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looking up the value corresponding to the last repetition in a ran | Excel Worksheet Functions | |||
| repetition cases | Excel Discussion (Misc queries) | |||
| repetition cases | Excel Programming | |||
| PivotTable - Repetition of rows | Excel Discussion (Misc queries) | |||
| Repetition of Code | Excel Programming | |||
Threaded Mode
