1

I'm using vba in Excel and I wish to loop through sets of numbers, but I wish to loop through a combination only once. I'm currently looping through all permutations, which cause combinations to be repeated. For example:

For i = 1 to 4
    'execute some code
    For k = 1 to 4
        'execute some code
    Next
Next

This will give the following combinations of i and j:

[1,1] dont want as 1 is repeated twice
[1,2] yes please
[1,3] yes please
[1,4] yes please
[2,1] dont want as [1,2] was already given, the order doesnt matter
[2,2] dont want as 2 is repeated twice
[2,3] yes please
[2,4] yes please
etc.

Catch my drift? The following post also deals with it, but it is unfortunately in Java and I can't make sense of the logic behind it to adapt it for vba in Excel: loop through combination once. I've simplified my explanation by using only two variables i and j, where in actual fact it is more combinations (up to 8 For loops) and they loop from about 100 to 900 which require extreme processing time to go through all permutations. Thus hoping to get some idea on how to go through only unique combinations, which will cut down the processing time significantly.

Any help will be much appreciated

Community
  • 1
  • 1
Gert Pienaar
  • 11
  • 1

1 Answers1

3

What that Java code does is start the inner loop from where the outer loop is up to, +1

For i = 1 to 4
    'execute some code
    For k = i + 1 to 4
        Debug.Print i, k
        'execute some code
    Next
Next

produces

1 2
1 3
1 4
2 3
2 4
3 4

chris neilsen
  • 52,446
  • 10
  • 84
  • 123