Generate random "set of 14" out of "3-digits"

[Kishan]

Using Excel 2000

Hi,

I want VBA the could generate "set of 14" out of "3-digits"

My 3-digit set is in the A2:A4 consists of "1X2"
In the cells A6:A45 filled number 1 to 40 this mean I need 40 random sets or it can be asked via input "how many set required"

First "set of 14" is generated in the B6:O6
Second "set of 14" is generated in the B7:O7 and continue till B45:O45

Example sheet...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	My Set
2	1
3	X
4	2
5	Generate	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14
6	1	1	1	2	X	1	1	1	1	1	2	1	1	X	1
7	2	2	1	2	X	2	2	1	2	1	2	X	X	2	1
8	3	1	1	1	2	2	1	2	2	1	1	X	1	2	2
9	4	1	2	1	2	1	1	1	2	1	1	1	1	X	1
10	5	1	2	1	2	1	1	2	X	1	X	2	1	2	X
11	6	2	1	1	1	X	1	2	X	2	2	1	2	X	2
12	7	X	2	1	1	2	X	1	X	1	1	X	2	2	2
13	8	2	1	1	2	1	1	1	1	X	1	X	1	1	1
14	9	X	2	2	2	1	2	1	X	2	X	1	X	2	1
15	10	1	1	X	1	2	1	1	X	1	1	1	2	2	1
16	11	2	1	X	X	1	1	2	2	X	1	2	1	1	1
17	12	2	X	2	X	1	1	2	X	X	2	1	1	1	X
18	13	1	1	2	2	X	2	2	1	1	1	1	1	2	1
19	14	X	1	1	2	2	1	1	X	X	1	X	1	1	X
20	15	2	1	1	2	X	1	2	X	X	2	1	1	2	1
21	16	1	X	2	1	2	X	2	1	X	1	2	X	X	2
22	17	X	2	2	1	2	1	X	1	1	1	1	1	2	X
23	18	1	2	2	X	2	1	2	1	X	1	2	2	2	X
24	19	1	1	2	1	1	2	1	1	1	1	1	1	2	1
25	20	2	1	1	X	X	2	X	1	X	X	1	1	1	X
26	21	X	1	X	1	2	1	2	X	1	X	1	X	X	2
27	22	1	X	1	2	X	X	X	1	1	1	1	1	1	2
28	23	X	2	2	X	2	1	1	1	1	X	1	1	1	2
29	24	X	1	1	2	1	2	1	1	2	2	1	X	1	X
30	25	2	1	X	X	2	2	2	2	X	1	X	2	1	X
31	26	2	1	2	1	1	2	2	2	1	2	2	X	1	X
32	27	1	X	X	1	1	1	X	X	2	1	X	2	X	1
33	28	2	1	2	X	X	1	X	1	1	1	2	2	X	2
34	29	2	2	2	1	1	1	X	1	1	1	2	X	X	2
35	30	1	1	1	X	2	2	2	1	1	2	2	2	X	1
36	31	1	X	1	1	2	1	2	1	2	2	X	1	2	X
37	32	1	1	2	1	1	X	1	X	2	X	1	1	X	2
38	33	2	2	1	X	1	2	2	1	2	1	2	1	2	2
39	34	2	X	X	X	2	1	2	X	2	1	2	2	2	X
40	35	2	1	X	2	2	2	2	1	X	X	1	X	X	X
41	36	1	2	X	X	2	1	2	1	2	1	1	2	1	2
42	37	X	X	1	1	2	1	1	1	X	1	X	2	X	2
43	38	1	1	2	2	1	1	2	X	2	X	X	2	1	1
44	39	X	2	1	1	2	2	1	2	1	1	1	1	1	1
45	40	1	1	2	X	1	1	X	X	1	1	2	2	1	2
46
47
48
Generate Random 1X2

Thank you in advance

Regards,
Kishan

 

[Kishan]

Give this code a try (you run the RandomCharacterSets macro (it will call the RandomizeArray function as it needs to)...

Hi RickRothstein, I am getting error '438' at line below

X2count =Application.RandBetween(LowerLimit, UpperLimit)

<o></o>
Please could you check?<o></o>
<o></o>
Thank you<o></o>
<o></o>
Regards,<o></o>
Kishan

 

[Rick Rothstein]

Hi RickRothstein, I am getting error '438' at line below

X2count =Application.RandBetween(LowerLimit, UpperLimit)

<o></o>

Sorry, I forgot about the version of Excel that you are using. Replace the code I gave you earlier with this code and then see if it works or not...

[table="width: 500"]
[tr]
	[td]Sub RandomCharacterSets()
  Dim X As Long, Z As Long, LowerLimit As Long, UpperLimit As Long, HowManySets As Long, ColCount As Long, X2count As Long, RowSet As String
  LowerLimit = 5
  UpperLimit = 9
  HowManySets = 40
  ColCount = 14
  For X = 1 To HowManySets
    X2count = Int((UpperLimit - LowerLimit + 1) * Rnd + LowerLimit)
    RowSet = String(ColCount - X2count, "1")
    For Z = 1 To X2count
      RowSet = RowSet & Choose(Int(2 * Rnd + 1), "X", "2")
    Next
    Cells(5 + X, "A").Value = X
    Cells(5 + X, "B").Resize(, ColCount) = RandomizeArray(Split(Trim(Replace(StrConv(RowSet, vbUnicode), Chr(0), " "))))
  Next
End Sub

Function RandomizeArray(ByVal ArrayIn As Variant) As Variant
  Dim cnt As Long, RandomIndex As Long, tmp As Variant
  Randomize
  If VarType(ArrayIn) >= vbArray Then
    For cnt = UBound(ArrayIn) To LBound(ArrayIn) Step -1
      RandomIndex = Int((cnt - LBound(ArrayIn) + 1) * Rnd + LBound(ArrayIn))
      tmp = ArrayIn(RandomIndex)
      ArrayIn(RandomIndex) = ArrayIn(cnt)
      ArrayIn(cnt) = tmp
    Next
  End If
  RandomizeArray = ArrayIn
End Function[/td]
[/tr]
[/table]

 

[Kishan]

Sorry, I forgot about the version of Excel that you are using. Replace the code I gave you earlier with this code and then see if it works or not...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub RandomCharacterSets()
  Dim X As Long, Z As Long, LowerLimit As Long, UpperLimit As Long, HowManySets As Long, ColCount As Long, X2count As Long, RowSet As String
  LowerLimit = 5
  UpperLimit = 9
  HowManySets = 40
  ColCount = 14
  For X = 1 To HowManySets
    X2count = Int((UpperLimit - LowerLimit + 1) * Rnd + LowerLimit)
    RowSet = String(ColCount - X2count, "1")
    For Z = 1 To X2count
      RowSet = RowSet & Choose(Int(2 * Rnd + 1), "X", "2")
    Next
    Cells(5 + X, "A").Value = X
    Cells(5 + X, "B").Resize(, ColCount) = RandomizeArray(Split(Trim(Replace(StrConv(RowSet, vbUnicode), Chr(0), " "))))
  Next
End Sub

Function RandomizeArray(ByVal ArrayIn As Variant) As Variant
  Dim cnt As Long, RandomIndex As Long, tmp As Variant
  Randomize
  If VarType(ArrayIn) >= vbArray Then
    For cnt = UBound(ArrayIn) To LBound(ArrayIn) Step -1
      RandomIndex = Int((cnt - LBound(ArrayIn) + 1) * Rnd + LBound(ArrayIn))
      tmp = ArrayIn(RandomIndex)
      ArrayIn(RandomIndex) = ArrayIn(cnt)
      ArrayIn(cnt) = tmp
    Next
  End If
  RandomizeArray = ArrayIn
End Function
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick Rothstein, your idea is brilliant

I like your code it is doing extra jobs than I request, as it can be controlled inside the code number of combinations, counts of X's & 2's, either between 5-9 or can be chosen fix 5-5.

Thank you with my bottom of heart giving a great solution

Have a nice time

Regards,
Kishan