VB Run-time error '9'; Subscript out of range

**lisabethvw** · 12-29-2009, 01:14 AM

Hello everyone:

First I want to say that the Recursion1.xls file that I downloaded from this site is a marvelous thing.

Next I want to say that my parade was rained on

earlier when I went to enter a list of 169 values and it came back with the above titled error.

I realize that increasing the range will cause the program to run even longer, but the need far outweighs the wait, believe me!

If anyone can explain to me in laymans terms how to increase the range to include a list of 169 entries or more, I would appreciate it. Attached is the current recursion file that I am working with.

I hope that someone can help me soon. I will be indebted to you forever, really!!

Thank you in advance!

Lisa

**shg** · 12-29-2009, 01:18 AM

No attachment. Zip if necessary.

... the Recursion1.xls file that I downloaded from this site ...

Where would we find that?

**lisabethvw** · 12-29-2009, 01:24 AM

The attachment is there at the bottom of the post as a .xls file. Do you want to me to edit it and upload it as a .zip file?

**shg** · 12-29-2009, 01:38 AM

The common algorithms require all numbers to be positive. The reason is that doing so allows the algorithm to abandon extending a selection when the sum already exceeds the target value; it decreases the search time significantly.

If you can recast the problem to avoid the negative numbers, perhaps the algorithm you have will work (I haven't looked at it), or I can provide another one (still subject to the all-positive numbers limitation).

**lisabethvw** · 12-29-2009, 01:49 AM

Originally Posted by shg

The common algorithms require all numbers to be positive. The reason is that doing so allows the algorithm to abandon extending a selection when the sum already exceeds the target value; it decreases the search time significantly.

If you can recast the problem to avoid the negative numbers, perhaps the algorithm you have will work (I haven't looked at it), or I can provide another one (still subject to the all-positive numbers limitation).

Please note that the recursion program works successfully with negative numbers as I have been using it all day today. The only time that I run into a problem of finding the target value is when my entries exceed a certain number.

While I understand what you are saying (for the most part

), there is or has to be a way to change the VBA code to just allow for more entries than what it was originally setup for or create a LBound/UBound function.....or something to the effect.

**lisabethvw** · 12-29-2009, 01:56 AM

[QUOTE=shg;2223138] it decreases the search time significantly.[QUOTE]

P.S. I truly am not concerned with search time because as I have indicated, the need far outweighs the wait.

**Marzuk** · 12-29-2009, 03:49 AM

The arrays are dimensioned with 30 elements. The values this produces exceed that. Simply increase the size of the arrays accordingly.

Edit:

Out of curiosity I've backtracked this type of problem a bit, just to see what the sheet was actually doing. I found several posts from SHG which referenced a couple other "solutions".

http://www.tushar-mehta.com/excel/te...ues/index.html

http://www.mrexcel.com/pc09.shtml

Note: the 'winner' of this contest ran his algorithm on an array with 54 elements and it took almost 4 hours. I'm guessing if you want to do it for 169 elements, you better be on vacation for a month, and pray no one turns your computer off! (expecting an exponential increase in time, instead of a linear relationship between elements and solution time, just a wild guess though)

EDIT2:

And yes, I do not expect either of these to handle negative numbers with grace (neither state either way specifically). Note the following quote:

On first examination, there could be 2^54 possible combinations - about 18 quadrillion - which would tend to take forever via a brute force method.

With this it becomes obvious that decreasing the problem space in any way possible becomes very important (hence the bit about the negative numbers).