Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement Modifications | Excel Worksheet Functions | |||
permutation | Excel Worksheet Functions | |||
turn off excel cell modifications? | Excel Discussion (Misc queries) | |||
How to Export as text file with NO delimiters or modifications? | Excel Discussion (Misc queries) | |||
webbrowser - excel - modifications | Excel Discussion (Misc queries) |