vba - Two array element combination in vb -


i want combine arrays in program. take example:

a = {1,2,3,4,5} b = {6,7,8,9,10} 

this should produce array containing first element of first array elements of second array except first element, {1,7,8,9,10}. should continue possible combinations, producing these output arrays:

{1,7,8,9,10} {6,2,8,9,10} {6,7,3,9,10} {6,7,8,4,10} {6,7,8,9,5}...   

for 2,3,4 elements of first array second array

{1,2,8,9,10} {6,2,3,9,10} {6,7,3,4,10} {6,7,8,4,5} {1,7,8,9,5}...    {1,7,3,9,10} {6,2,8,4,10} {6,7,3,9,5} {1,7,8,4,10}..... 

and vice versa second array.

for first combination i've tried:

for = 0 4  'first array loop      j = 0 4      'second array loop          if <> j             arr(j) = arr2(j)          else             arr(j)=arr1(j)          end if      next  next 

this give every combination possible:

function fifth(paramarray arr() variant) variant()  dim temp() variant dim long dim j long dim t long = lbound(arr) + 1 ubound(arr)     if ubound(arr(lbound(arr))) <> ubound(arr(i))         msgbox "arrays not same size"         exit function     end if next redim temp(1 (ubound(arr) + 1) ^ (ubound(arr(lbound(arr))) + 1), lbound(arr(lbound(arr))) ubound(arr(lbound(arr)))) variant = 1 (ubound(arr) + 1) ^ (ubound(arr(lbound(arr))) + 1)     j = 1 (ubound(arr(lbound(arr))) + 1)         t = int((i mod ((ubound(arr) + 1) ^ j)) / (((ubound(arr) + 1) ^ j) / (ubound(arr) + 1)))         temp(i, j - 1) = arr(t)(j - 1)     next j next  fifth = temp  end function 

you call thus:

sub arrcombine()  dim arr1() variant dim arr2() variant dim rsltarr() variant  arr1 = array(1, 2, 3, 4, 5) arr2 = array(6, 7, 8, 9, 10)  rsltarr = fifth(arr1, arr2)  activesheet.range("a1").resize(ubound(rsltarr, 1), ubound(rsltarr, 2) + 1).value = rsltarr  end sub 

it output on active sheet, this:

enter image description here

this has advantage of being dynamic. can use more 2 arrays. rule need have same number of values.

the other limit number of rows on worksheet. (number of arrays) ^ (number of values) cannot exceed 2^20.


Comments

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

css - Make div keyboard-scrollable in jQuery Mobile? -

ruby on rails - Seeing duplicate requests handled with Unicorn -