Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create an Array that includes every possible combination for 4 gro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create an Array that includes every possible combination for 4 gro
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 -- Hope that helps. Vergel Adriano "Bob K" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create an Array that includes every possible combination for 4 gro
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 "Bob K" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create an Array that includes every possible combination for 4 gro
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 "RB Smissaert" wrote in message ... 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 "Bob K" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combination of AutoFilter and array formula? | Excel Worksheet Functions | |||
Combination of functions for a conditional format and an array | Excel Worksheet Functions | |||
Create a "recap" worksheet that includes all info from all worksh. | Excel Worksheet Functions | |||
Creating a Combination or Permutation Array in Excel | Excel Programming | |||
Code to create Email that includes quotes | Excel Programming |