I'm trying to scale up the code found here. To run it you put a 0 in column'B' for each row, and in C9 the SUMPRODUCT formula **=SUMPRODUCT(A1:A12*B1:B12)**. When the ABC sub is run column 'D' should list all the combinations that add to a desired result, which I have hard-coded into the sub as 24. I believe the list should be

10,10,4

12,12

11,1,12

11,12,1

13,11

8, 16

but when I run the code 10,10,4 and 8,16 are missing. Maybe that's because they involve the last few rows, but if so I can't find the reason why. In my opinion I'm a pretty good VBA programmer and I understand how SUMPRODUCT works, but I've spent*days* on this and I can't get it to work properly and it's driving me bonkers!

So what I'm looking for here is a correction to the code and an explanation of how the binary matching that the code uses works. I'm using Excel 2010.

Bonkers.xlsm

10,10,4

12,12

11,1,12

11,12,1

13,11

8, 16

but when I run the code 10,10,4 and 8,16 are missing. Maybe that's because they involve the last few rows, but if so I can't find the reason why. In my opinion I'm a pretty good VBA programmer and I understand how SUMPRODUCT works, but I've spent

So what I'm looking for here is a correction to the code and an explanation of how the binary matching that the code uses works. I'm using Excel 2010.

Bonkers.xlsm

I placed a heap of zero values in column B and the formula in C9.

Then I created the macro as showing on the linked webpage.

What data should I be putting into column A before I run the macro?

Stay with me.....

(and yes, judging by your experience in programming and your ranking in EE, I guessed it was not homework LOL)

I increased the value set to wf.Dec2Bin(i, 10) and that allows one extra number to come into the calculations (4)

My guess now is that we have to increase the value if "i" in that declaration by extending the range of the "for next loop

'For i = 0 To 511 '

Please stand by (I might be completely on wrong track).......

working...working.....thin

Chris

It cannot handle very large numbers (unless your processor is of extraordinarily powerful).

So I am writing my own converter and will try to see if that helps.

I had noticed that the number "4" in the first column was included in the run

(a "1" appeared against it in column B) when I increased the upper limit in the "For i = 0 to 511"

Loop . However it kept falling over at a certain point since the number "i" became too large for the

Dec2Bin() function to handle.

Be right back with the amended code. (Fingers crossed)

Chris

The amended code is attached.

I have changed a few things:

1. Changed the type of the variable "i" to a "Double" (required when using my new function called MyDec2Int)

2. Changed the upper limit of the "Loop" to 765 (something that Excel's internal Dec2Int() cannot handle, its max is 511)

3. Added a "Contenders" variable, which simply allows you to increase/decrease the number of values in column "A" as you wish

without having to change the code.

4. Added a "CountNonBlanks" function to ascertain how many numbers are up for inclusion in calculation (storing value in "Contenders")

5. Added a "MyDec2Bin" to replace wf.Dec2Bin (the "buggy" worksheet function Dec2Bin)

6. Added a "ZeroPadLeft" so that the function "MyDec2Bin" returns correctly formatted (leading zeros) value from MyDec2Bin

7. Now using the value stored in the spreadsheet in cell "E1" tas the "total" you which to use when multiple numbers are added together (24 was hardcoded previously)

The user can now decide on a different total without having to change the code.

The problem was inherent in the limitations placed on the Excel function Dec2Bin by the processor.

Obviously they have written that function to handle only a certain size numeric in binary format (no more than 9 digits in the binary number).

We needed a 12 digit number size.

The results I get back are as follows:

There are still problem with the values in Column "B", so I suspect the results in Column C are not "spot on" yet.

I will look further into the code to see why the values in columns B and C are still not accurate and then get back to you, but in the meantime

there is some light at the end of the tunnel.

A handy reference I used in my calulations is:

Binary Calculator

My thanks to "MacroWriter" on the following StackOverflow page for his terrific function "cn" (convert number) which I fiddled with to come up with

MyDec2Bin (it was returning incorrectly formatted string so I had to come up with "PadZerosLeft" to rectify that.

Code page that led to the enhanced function MyDec2Bin (based on the code "cn()" by MacroWriter

The code in Bonkers2.xlsm is currently as follows:

```
Option Explicit
Sub ABC()
Dim i As Double, s As String
Dim j As Long, K As Long
Dim answer As String
K = 1
'some new lines added to ascertain how many numbers we are considering in column A
Dim NB As Range
Set NB = Application.Range("A1:A100")
Dim Contenders As Integer
Contenders = CountNonBlanks(NB)
Dim nLimit As Long
nLimit = (3 * 255) '765
For i = 0 To nLimit
'The length of the returned string to be stored in s is set at 12.
' Although the size of a binary to hold the value 765 is 11, lets make it 12 just to be safe!
s = MyDec2Bin(i, 2, 12) 'call internal function to obtain extended Binary number (up to 12 digits, with a maximium of 35!)
For j = 1 To Contenders
Cells(j, 2).Value = Val(Mid(s, j, 1))
Next j
If Range("C13").Value = Range("E1").Value Then 'Cell E1 contains the desired total at which we wish to arrive
answer = ""
For j = 1 To Contenders
If Cells(j, 2) = 1 Then answer = answer & "," & Cells(j, 1)
Next j
Cells(K, 4) = Mid(answer, 2)
K = K + 1
End If
Next i
End Sub
Private Function CountNonBlanks(WorkRng As Range) As Integer
Dim total As Integer
Dim rng As Range
For Each rng In WorkRng
If Not IsEmpty(rng.Value) Then
total = total + 1
End If
Next
CountNonBlanks = total
End Function
Private Function MyDec2Bin(ByVal n As Double, ByVal Base As Double, ByVal nLen As Integer) As String
'n the number to convert
'Base is the numberic system to which "n" is to be converted.
'This function can convert to binary all the way to the length of the
'digits string showing in the code below (maximum of 36!)
Dim x As Double 'The exponent without decimals
Dim xx As Double 'The exponent with decimals, if any
Dim r As String 'The return string
Dim p As Integer 'Posistion of the digit in the return string
Dim L As Long 'Length of the string return string
Dim d '(d+1) because mid() does not accept 0.
'The position of the digit in the digits string.
Dim v As Double 'The numeric value of the position
'of the digit in the return string
Dim digits As String
digits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Start:
If n > 0 Then
xx = Log(n) / Log(Base)
x = Int(xx)
End If
p = x + 1
If r = "" Then
r = String(p, "0")
L = p
End If
v = Base ^ x
d = n \ v
Mid(r, L - x, 1) = Mid(digits, d + 1, 1)
n = n - (v * d)
If n <> 0 Then GoTo Start
MyDec2Bin = ZeroPadLeft(r, nLen)
End Function
Private Function ZeroPadLeft(StrIn As String, nLen As Integer) As String
Dim nZeros As Long
Dim strOut As String
nZeros = (nLen - Len(StrIn))
If nZeros < 0 Then nZeros = 0
ZeroPadLeft = String(nZeros, "0") & StrIn
End Function
```

Results2.jpgBonkers2.xlsm

I notice you claim the follow two combinations should be showing:

11,1,12

11,12,1

If that is so, surely "1,12,11", "1,11,12", "12,11,1" and "12,1,11" should also be showing.

Do you want it so that the combinations are distinct? (I think double-ups are caused by the numbers being used more than once in Column A)

I COULD get the code to ignore duplicate combinations, as distinct from permutations.....or it the other way around? (LOL....The Uni days go back about 55 years!)

Further comparisons of my results so far show that I am missing the two values "11,1,12" and "11,12,1" showing in your listing

and YOU are missing the two values "12,4,8" and "12,4,8" (identical) showing in my listing.

I will have to go off to bed now, since I have to get up for my Bridge game starting at 10.00am (and it takes an hour to drive there) so I have to get up

after at least 8 hours sleep. It is 12:16am and I think I have put in about .12 hours work on this (better get something to eat before I hit the sack).

I will continue on tomorrow afternoon when I get home from Bridge.

In the meantime, please clarify my query re repeating "combinations" (i.e. same numbers but in different order) and why the values "12,4,8" were not

included in your list of proposed valid results.

Cheers

Chris

It was just that those values 12,4 and 8 were not in your original list.

I will put a process in place to not list a combination if it (or one of its its permutation's is already listed).

I will resume work on this in about 12 hours when I get back home. (I am finding this really stimulating, actually).

Night..night..

Chris

This one is on us!

(Get your first solution completely free - no credit card required)

UNLOCK SOLUTION
I was actually thinking of an even simpler method (while I slept)! and was keen to have a go at replacing all this with a simple function involving array manipulation.

I notice though that you have awarded me 500 points, and then also noted that you have offered me a chance for objection.

Under this system (all very new to me after being away for so long) how do I lodge an objection and would I earn any more points by doing so anyway.? Is 500 about all I will get if an objection is upheld? Points are not the main thing of course, just a happy outcome for all concerned.

Please advise the situation re objection.

If it is alright with you, I would like to simplify all this procedure and replace all this code with an alternative call to a new function which

could then work, (with or without a spreadsheet) . If so, I will post that function under this question as a follow-up.

I just also noticed a bit of "jimmying" in your own written function Dec2Bin where you actually opt out with an

"Error - Number too large for bit size".

My code bypassed that problem, since it allowed any size number, so I am a bit surprised you allow this restriction back in.

Anyway, it still is a challenge, and I will ponder on it until I find a solution I am totally happy with (just for the record).

Hopefully you will allow me to then post it to this message.

Cheers

Chris

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.