Macro for summing certain numbers???
Posted by Tim Johnson on November 22, 2001 7:37 AM
Is there a formula or macro that will help me with the following:
2
5
4
7
3
5
---
26
===
Within the list of above numbers, is there a way for Excel to show me the possible combinations that sum up to 10? For example, highlight 5 & 5 in one color, 7 & 3 in another and 2, 3 & 5 in another?
Here it is not too difficult to see the results yourself, but when the numbers are 4,598,258,289.92 it becomes nearly impossible.
THANKS SO MUCH IN ADVANCE!!!!!!!!!!!!
Posted by s-o-s on November 22, 2001 4:14 PM
Hi Tim,
Here is a quick and dirty macro that will do the job but it can be improved it will calculate for up to 3 variable suming together, hopefully you will be able to replicate the process if you need to add further possible combinations.
I have set 100 as the limit of cells to be summed change this upwards if necessary, but more cells just means more longer solutions.
Colouring will not work as the cells become overwritten with new solutions for now I am just throwing them on the screen in a message box.
Change your target value to any number desired other than 10.
-----
Sub test_loop()
Dim a(100) As Integer
Targt = 10
Sol1 = ""
n = 0
For Each cell In Selection
n = n + 1
a(n) = cell.Value
If a(n) = Targt Then Sol1 = Sol1 & a(n) & Chr(10)
Next
MsgBox Sol1, vbOKOnly, "Solutions with 1 Variable"
Sol2 = ""
For r = 1 To n
For s = 1 To n
If r = s Then GoTo nxt2
If a(r) + a(s) = Targt Then Sol2 = Sol2 & a(r) & "+" & a(s) & Chr(10)
nxt2:
Next
Next
MsgBox Sol2, vbOKOnly, "Solutions with 2 Variable"
Sol3 = ""
For r = 1 To n
For s = 1 To n
For t = 1 To n
If r = s Then GoTo nxt3
If r = t Then GoTo nxt3
If s = t Then GoTo nxt3
If a(r) + a(s) + a(t) = Targt Then Sol3 = Sol3 & a(r) & "+" & a(s) & "+" & a(t) & Chr(10)
nxt3:
Next
Next
Next
MsgBox Sol3, vbOKOnly, "Solutions with 3 Variable"
End Sub
-----
I hope this helps.
s-o-s
=============
Posted by Tim Johnson on November 22, 2001 7:54 PM
I messed around with this a bit and it doesn't quite do the trick, but it is close. It doesn't seem to work with decimal numbers, like $15.23. Also, is there a way to change the target number without having to go inside the macro code? Is there a way to have the first message box ask for the target number? THANKS AGAIN IN ADVANCE!!!!!
Posted by s-o-s on November 23, 2001 1:16 AM
In response to your first point, It is probably falling down when the difference is less than 0.001.
If you change each line to give you something similar to the line below it may solve the problem...
If abs(a(n) - Targt) < 0.01 Then Sol1 = Sol1 & a(n) & Chr(10)
This will give you a tolerance that it will make Excel accept your sums with decimals. You can adjust the 0.01 up or down until you get the desired result.
-----
To get around the variable target use the code below
Dim Targt as Double
Targt = InputBox("Enter Target")
-----
Hope this helps
s-o-s
==================
Posted by Tim Johnson on November 23, 2001 10:31 AM
It is so close!!! I got the input box to work so I could enter the target without going into the code and that works great!! But it still does not seem to work with decimals. I guess I am unclear as to your instructions onhow to fix this. Could you please edit the original code you gave me to include the fix? THANK YOU VERY MUCH FOR ALL YOUR HELP!!!!
7.33
8.17
5.50
10.00
-----
31.00
=====
want to show the sums that add to $15.50...
