Create an Array that includes every possible combination for 4 gro

[Guest]

Using the following codes below I am trying to create an excel file that
contains ever possible combination for the four groups. For example: X22E,
X11G, C211 V21G. Any help would be much appreciated in developing a macro to
accomplish this.

thanks,

bob


Class Form Pricing Group
X 1 1 G
C 2 2 E
V 3 3 I
D 4 M
M N
R F

 

[Guest]

Bob,

something like this would put all possible combinations of values in columns
A to D in column E.

Sub test()
Dim colA As Range, cellA As Range
Dim colB As Range, cellB As Range
Dim colC As Range, cellC As Range
Dim colD As Range, cellD As Range
Dim lRow As Long

Set colA = Range(Range("A2"), Range("A65535").End(xlUp))
Set colB = Range(Range("B2"), Range("B65535").End(xlUp))
Set colC = Range(Range("C2"), Range("C65535").End(xlUp))
Set colD = Range(Range("D2"), Range("D65535").End(xlUp))

lRow = 2
For Each cellA In colA
For Each cellB In colB
For Each cellC In colC
For Each cellD In colD
Range("E" & lRow).Value = cellA.Text & cellB.Text &
cellC.Text & cellD.Text
lRow = lRow + 1
Next cellD
Next cellC
Next cellB
Next cellA
End Sub

 

[RB Smissaert]

This is a pure VBA solution:

Sub test()

Dim i As Long
Dim n As Long
Dim c As Long
Dim f As Long
Dim p As Long
Dim g As Long

Dim Class
Dim Form
Dim Pricing
Dim Group

Dim arrRow(0 To 3)
Dim arrResult
Dim coll As Collection

Set coll = New Collection

Class = Array("X", "C", "V", "D", "M", "R")
Form = Array(1, 2, 3, 4)
Pricing = Array(1, 2, 3)
Group = Array("G", "E", "I", "M", "N", "F")

For c = 0 To 5
For f = 0 To 3
For p = 0 To 2
For g = 0 To 5
arrRow(0) = Class(c)
arrRow(1) = Form(f)
arrRow(2) = Pricing(p)
arrRow(3) = Group(g)
coll.Add arrRow
Next g
Next p
Next f
Next c

ReDim arrResult(1 To coll.Count, 1 To 4)

For i = 1 To coll.Count
For n = 1 To 4
arrResult(i, n) = coll(i)(n - 1)
Next n
Next i

'to test the array
Range(Cells(1), Cells(coll.Count, 4)) = arrResult

End Sub


RBS

 

[RB Smissaert]

Actually as it is simple to calculate the number of all possible
combinations
you can do without the collection and that will make it faster:

Sub test()

Dim i As Long
Dim c As Long
Dim f As Long
Dim p As Long
Dim g As Long
Dim lCombinations As Long
Dim Class
Dim Form
Dim Pricing
Dim Group
Dim arrResult

Class = Array("X", "C", "V", "D", "M", "R")
Form = Array(1, 2, 3, 4)
Pricing = Array(1, 2, 3)
Group = Array("G", "E", "I", "M", "N", "F")

lCombinations = (UBound(Class) + 1) * (UBound(Form) + 1) * _
(UBound(Pricing) + 1) * (UBound(Group) + 1)

ReDim arrResult(1 To lCombinations, 1 To 4)

For c = 0 To 5
For f = 0 To 3
For p = 0 To 2
For g = 0 To 5
i = i + 1
arrResult(i, 1) = Class(c)
arrResult(i, 2) = Form(f)
arrResult(i, 3) = Pricing(p)
arrResult(i, 4) = Group(g)
Next g
Next p
Next f
Next c

'to test the array
Range(Cells(1), Cells(lCombinations, 4)) = arrResult

End Sub


RBS