Need help with algorithm

  • Thread starter Thread starter RADO
  • Start date Start date
R

RADO

I am stuck, will much appreciate advice.

I have several vertical ranges with several values in each. I need to
construct their possible combinations. For example, if range 1 contains
values 1, 2 , range 2 : A, B, range 3: X, Y, Z then the result should look
like this:

1 A X
1 A Y
1 A Z
1 B X
1 B Y
1 B Z
2 A X
2 A Y
2 A Z
2 B X
2 B Y
2 B Z

I am trying to come up with a general algorithm to construct this output in
Excel (because I don't know the number of ranges and number of values in
them in advance, they vary). It looks like a job for the recursion, but I
could not figure out how. Please help!

Thanks in advance -
RADO
 
A start hopefully..


Sub a()
Dim ArrCounter1 As Long
Dim ArrCounter2 As Long
Dim ArrCounter3 As Long
Dim Arr1 As Variant
Dim Arr2 As Variant
Dim Arr3 As Variant
Dim RowCounter As Long
Arr1 = Array(1, 2)
Arr2 = Array("A", "B")
Arr3 = Array("X", "Y", "Z")
For ArrCounter1 = 0 To UBound(Arr1)
For ArrCounter2 = 0 To UBound(Arr2)
For ArrCounter3 = 0 To UBound(Arr3)
RowCounter = RowCounter + 1
Cells(RowCounter, 1).Value = _
Arr1(ArrCounter1) & Arr2(ArrCounter2) & Arr3(ArrCounter3)
Next
Next
Next
End Sub
 
Back
Top