Run-time error 6 overflow

[josros]

I have this code and giving me this error "Run-Time error 6, overflow"

here is the line of code highlighting the error:

Please Login or Register  to view this content.

Complete code:

Please Login or Register  to view this content.

thank you,

[karedog]

The error message itself has described what is actually happened : overflow, the variable type cannot hold the number to be assigned.

You describe i as a long datatype :

Please Login or Register  to view this content.

and if in VBE editor you select/highlight the Long word and then press F1, the help window will show this :
Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647

So maximum value the variable i can hold here is 2,147,483,647, or (2 ^ 31) - 1, which mean UBound(aToUse) is greater than 31

[Kaper]

First question is the size of Range("rngList") I suspect it's too large. You are trying to generate Long Interer as 2^this_size
So I think 31 is the limit.

[josros]

thank you.

is there anyway to increase it to 100, sorry no that good in VBA (file attached)

[karedog]

If the question is, can we use a trick to accomplish this, the answer is : Yes, I'll show you how :

Insert a new module and paste this code :

Please Login or Register  to view this content.

If we run the sub Test_Long, then there is no problem at all, because variable i is defined as long, and long datatype can hold number 1000.

But if we run sub Test_Byte_A, the overflow error will be shown up just as your case, because b1 is defined as byte (with its range is between 0 and 255), so b1 here cannot hold number 1000

Now if we run sub Test_Byte_B, we use double loop, we divide 1000 into two parts : 0 to 4 (total = 5) and 1 to 200 (total =200), so grand total = 5 * 200 = 1000, and the result of this sub Test_Byte_B will be same as sub Test_Long, so this technique can be used to overcome the problem.

=================================================================================================================================

But here comes the real problem : ***IT IS VERY SLOW*** if you want to solve the problem with big data using your current approach.

Let me give an example, on your MBinary module (the one where Sub SumToTarget() is located), add this code (which its code is just a small part of Sub SumToTarget) :

Please Login or Register  to view this content.

You can see, we only try to use 20 cells only --> (2 ^ 20)
The result of the code on my PC is :
Start at : 20:49:06
Stop at : 20:49:29

or in other word, it takes about 25 seconds for 20 cells (--> 2 ^ 20)


Now let's say we want to calculate using your data on sheet "FIND_TARGET", which has 75 cells of data.
So roughly, we can guess the time needed (compared to our sub Test_Time result) will be :
= 25 seconds * (2 ^ (75 - 20))
= 9.00719925474099E+17 seconds
= 9.00719925474099E+17 / (60 * 60 *24) days
= 10,424,999,137,432 days

You see, this is just a nonsense, just don't do it using this approach.

=================================================================================================================================

So now, how to solve the problem ?
Using VBA alone (which is just a scripting language) is just too slow, we must find another approach.
I see many people here using Excel built in Solver, or third party addin OpenSolver to solve this kind of problem.
As a compiled code, Solver works very very much faster compared to VBA.
We here in ExcelForum have some very talented Experts who are mastering in this field of Solver.
So my suggestion is, you open a new thread which expect using solver to solve your problem, the title would be something like :
"How to use solver to achieve specific Target Sum", and hopefully our experts will be shown up to help you with your case.



Regards

[sintek]

What is it you actually are wanting to achieve here...
What is expected output required?
Extract all figures that add up to 25865.47?

[josros]

Thank you.

[sintek]

Thank you.

Response to Post 6?

[josros]

YSorry, yes this:

Extract all figures that add up to 25865.47?

Thanks again

[sintek]

Extract all figures that add up to 25865.47?

This will give you an idea...Can be amended for other output...
All figures adding up to ..... will be highlighted in red....

Please Login or Register  to view this content.