# Run-time error 6: Overflow - Trying to generate list of combinations

1. ## Run-time error 6: Overflow - Trying to generate list of combinations

Hello, I'm running into a problem and am wondering if there's an easy way to get around this.
I have three columns of information and I'm trying to get every possible combination. This works when the lists are small, however when I use my actual data - I get an overflow error.
These are the lengths of my list:
Column1 = 6
Column2 = 90
Column3 = 389

This is my code:
``Please Login or Register  to view this content.``
Is there any easy way around this?
I get this error after it generates about 32,767 rows..

Column2 is also dependant on Column1... ie: if Column1 value was A, column2 selection can be reduced to a much smaller amount -- this could potentially help, but I'm unsure of how to code this in - I adapted this code that I'm using from doing some google searching and extensive trial & error.

Thanks for any suggestions.

2. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Change Integer to Long

3. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Thanks for the suggestion - unfortunately that did not affect the outcome.
Same error after the same amount of results were populated.

4. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Post a workbook that demonstrates the error.

5. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

This code worked for me. It generated 210,060 different permutations. (6x90x389 = 210,060)

(trivia: Since the order matters, it's permutations, not combinations. A combination lock should really be called a permutation lock.)

http://www.mathsisfun.com/combinator...mutations.html

``Please Login or Register  to view this content.``

6. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Note: It takes about 30 seconds to generate the list on my laptop. But then again, I have a very slow work laptop. At home I'll bet my PC can generate the list is much less time. Be sure to include Application.ScreenUpdating = False (and then True), or it will even take longer to run.

7. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Originally Posted by Ed_Collins
Note: It takes about 30 seconds to generate the list on my laptop. But then again, I have a very slow work laptop. At home I'll bet my PC can generate the list is much less time. Be sure to include Application.ScreenUpdating = False (and then True), or it will even take longer to run.
Ed,
Could I suggest you do the job with memory arrays rather than worksheet cells.
I think you'd find it a lot faster. And you wouldn't need screen updating.
``Please Login or Register  to view this content.``

8. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Very, very nice.

I've never, ever used that resize property before. Wasn't even aware of it. I'm going to have to take a look at that. Thanks.

9. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Okay, I'm still not sure what is going on or why that works.

I understand storing the x, y, and z values in a two dimensional array called a. That much is easy and clear.

But then you reference the array when done, without specifying which element. That I've never done with an array before. And you use this resize property, using the variable i, (which after the three FOR NEXT loops are finished, should be equal 210061) And after doing so then suddenly all cells are filled in, instantly, albeit down a row from where it was before.

So what's all going on?

10. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Never mind. I finally played around with it and broke it all down and figured it all out.

Again, I've never used resize before. Pretty slick. Thanks.

This might have made a tiny, tiny bit more sense to me, initially.

``Please Login or Register  to view this content.``
Notice I dimension the array only as large as intended. (It confused me, for a minute, as to why the array was dimensioned the way that it was.) I also reference the entire array this way: a() which I thought was required. (Never seen it referenced without the parenthesis before. Finally, if the user wanted the permutations to begin in row 2, the above code does that.

11. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Originally Posted by Ed_Collins
Never mind. I finally played around with it and broke it all down and figured it all out.

Again, I've never used resize before. Pretty slick. Thanks.

This might have made a tiny, tiny bit more sense to me, initially.

...
...

Notice I dimension the array only as large as intended. (It confused me, for a minute, as to why the array was dimensioned the way that it was.) I also reference the entire array this way: a() which I thought was required. (Never seen it referenced without the parenthesis before. Finally, if the user wanted the permutations to begin in row 2, the above code does that.
@Ed,

Glad that you, and hopefully the OP, found the demo interesting and maybe useful.

My 1 million row dimension was only because I didn't bother to multiply 6*90*389. However sometimes the minimum array size isn't known in advance, so this must be handled however best (say by arbitrary allocation, by ReDim Preserve, etc.)

The 10^6 did set aside somewhat more computer memory than the minimum needed for this problem, but with the abundant memory on computers these days it mattered but little unless maybe part of a larger code. It made negligible or no difference to the speed of executing this code.

Screen updating disabled if anything slows this type of code slightly. Only if you're using worksheet cells or worksheet arrays is it useful.

Dim a(of whatever dimensions) tells the VBA that you're using a memory array of whatever dimensions.

But, once declared (or dimensioned), the VBA knows what you mean by a , so the parentheses become redundant as far as the entire array is concerned. Use them or not, just as you like. Parts of it though do need to be specified, such as a(i,3).

Also, my modification of your code above to use memory array rather than cells actually required Dim a(1 to 210061, 1 to 3) because you started with i=2 (I tried to modify your code as little as possible). Your later version didn't do this so 210060 is OK.

12. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Understood.

I learned something today. Thanks again.

13. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

Thanks for all the help and the additional techniques...
For my purposes, the Application.ScreenUpdating = False stopped the Overflow, however - it does take about 30 seconds to run.
I'll play around with this second set of code to see if I can speed things up. Thanks for your help everyone, very much appreciated.

14. ## Re: Run-time error 6: Overflow - Trying to generate list of combinations

kalak's code populates the cells instantly on my machine - there is no delay whatsoever. Prior to this my prior code took 15 seconds to run on my PC here at home. (I knew my home PC was much faster than my laptop.)

However, I don't understand why Application.ScreenUpdating = False stopped your overflow message. It shouldn't have.

There are currently 1 users browsing this thread. (0 members and 1 guests)