[yauchildchew]

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.

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

Here I have attached a dummy file, and I would like to remind that the codes in the VBE is a mess and not done in a structured way. I hope I could but not now though, still learning.
Here is what I try to achieve.

1) through inputs from the form"fmmain" and form "fminput", the selected item-lists (K01, K02, K03.... whichever selected) from sheet"demand", will be copied to sheet"selected demand"

2) there is a variable called "housenum", which determines the number of item-lists to form a combination.

Say, "housenum" is 3, and number of available item-lists is 8, the total possible combinations is 3^8=6561. However, if the combinations do not allow repetition (eg. ACC, CAC, CCA will be counted as one), the total will become (8+3-1)!/3!/(8-1)!=120.

This is what i need: to generate the combinations without repetition.

3) Say now i have 120 combinations. In each combination, say "K01,K01,K02", I will sum up each data from all the item-lists accordingly, and produce the result in sheet"dmdcombine", in a different direction though (as row limit 1048576 is much larger than column limit).

Please kindly enlighten.
Thanks.