Modifications to Permutation Macro
 

Hi,
I am new to VBA programming but I am working on a tight
deadline, so I don't have a lot of time to experiment and
your help is indeed very much appreciated!

Someone was kind enough to provide the following statement
to me, which permutates 3 arrays of numbers (see the
bottom of the message). However, I would like to make
three modifications to the statement, which I think are
really simple to make (I just don't know how):

1) Rather than simply typing in the 3 arrays, I would like
the macro to pick-up the arrays from three ranges in
sheet1 (e.g. A1:D1:, A2:D2, A3:D3);

2) I would also like the output in sheet2;

3) And, finally, rather than getting the output in
a "a*A*1" format, I would like the "a" to go in column A,
the "A" to go in column "B" and the "1" in column C.

Again, thank you very much for your help,
Henrik


Sub Permutate()
arr1 = Array("a", "b", "c", "d")
arr2 = Array("A", "B", "C", "D")
arr3 = Array("1", "2", "3", "4")
rw = 1
For i = LBound(arr1) To UBound(arr1)
For j = LBound(arr2) To UBound(arr2)
For k = LBound(arr3) To UBound(arr3)
Cells(rw, 1) = arr1(i) & "*" & _
arr2(j) & "*" & arr3(k)
rw = rw + 1
Next: Next: Next
End Sub

Modifications to Permutation Macro
 

Sub Permutate()
With Worksheets("Sheet1")
arr1 = .Range("A1:D1").Value
arr2 = .Range("A2:D2").Value
arr3 = .Range("A3:D3").Value
End With
rw = 1
For i = LBound(arr1, 2) To UBound(arr1, 2)
For j = LBound(arr2, 2) To UBound(arr2, 2)
For k = LBound(arr3, 2) To UBound(arr3, 2)
With Worksheets("Sheet2")
.Cells(rw, 1) = arr1(1, i)
.Cells(rw, 2) = arr2(1, j)
.Cells(rw, 3) = arr3(1, k)
End With
rw = rw + 1
Next: Next: Next
End Sub


--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy

"Henrik" wrote in message
...
Hi,
I am new to VBA programming but I am working on a tight
deadline, so I don't have a lot of time to experiment and
your help is indeed very much appreciated!

Someone was kind enough to provide the following statement
to me, which permutates 3 arrays of numbers (see the
bottom of the message). However, I would like to make
three modifications to the statement, which I think are
really simple to make (I just don't know how):

1) Rather than simply typing in the 3 arrays, I would like
the macro to pick-up the arrays from three ranges in
sheet1 (e.g. A1:D1:, A2:D2, A3:D3);

2) I would also like the output in sheet2;

3) And, finally, rather than getting the output in
a "a*A*1" format, I would like the "a" to go in column A,
the "A" to go in column "B" and the "1" in column C.

Again, thank you very much for your help,
Henrik


Sub Permutate()
arr1 = Array("a", "b", "c", "d")
arr2 = Array("A", "B", "C", "D")
arr3 = Array("1", "2", "3", "4")
rw = 1
For i = LBound(arr1) To UBound(arr1)
For j = LBound(arr2) To UBound(arr2)
For k = LBound(arr3) To UBound(arr3)
Cells(rw, 1) = arr1(i) & "*" & _
arr2(j) & "*" & arr3(k)
rw = rw + 1
Next: Next: Next
End Sub