Create Combinations If Sum Matches

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,353
Office Version
  1. 2010
Hello,

I was using this macro but now I need a new one

In the screenshot I have shown only 3 combinations are OK rest are NOT VALID.

I want macro pick only 1 number from each row and make combinations of 5 numbers if sum target match with cell A1 given value. How do I want it should work?
Example:
1- picks 1st number from row 3 which is =7
2- picks 2nd number from row 4 which is =58
3- picks 3rd number from row 5 which is =58
4- picks 4th number from row 6 which is =25
5- picks 5th number from row 7 which is =98
As these 5 numbers sum is 7+58+58+25+98 =246 so far first 4 number remain the same than macro continue pick next number in the row 7... 5th number and go for next pick 19, 35, 69, 78, 33, 2, 29, 17, 96, and 6 non of these 5th number adding as total does not match...
Now next macros go for next 4th number in the row6, which is find 58... but here is a trick I want macro to pick 5th number from row 7 but 19 (not the 98 which is starting number but not next to 58 from the row 6 so far next picking number has to be first from row 7.... and this is what not doing the attached macro)

Please I need a new VBA that could work with my new sum strategy.

196Choose Delay Patterns To Play Sum
Partidon1n2n3n4n5n6n7n8n9n10n11n12n13n14n15c1c2c3c4c5Note
1
7​
21​
9​
35​
39​
5​
34​
23​
37​
15​
6​
75858469NOT VALID
2
58​
19​
1​
38​
34​
9​
65​
6​
17​
49​
3​
758257729OK
3
58​
25​
5​
76​
65​
4​
1​
86​
7​
34​
24​
75845869NOT VALID
4
25​
58​
98​
19​
77​
21​
45​
76​
2​
4​
3​
75849829NOT VALID
5
98​
19​
35​
69​
78​
33​
2​
29​
17​
96​
6​
758341978NOT VALID
719587735NOT VALID
719257669NOT VALID
719762569NOT VALID
719767717OK
719657629OK
719345878NOT VALID
719247769NOT VALID
71767735NOT VALID
71652598NOT VALID
71654578NOT VALID
7186498NOT VALID
71345896NOT VALID
71347678NOT VALID
738585835NOT VALID
738587617NOT VALID
73857769NOT VALID
738765817NOT VALID
738349819NOT VALID
738341998NOT VALID
738342196NOT VALID
738245869NOT VALID
738249829NOT VALID
734581978NOT VALID
73476772NOT VALID

VBA Code:
Sub Create_Combinations_If_Sum_Matches()

y = 20 'Start Combination from Column 20
    
Dim x1 As Long
Dim x2 As Long
Dim x3 As Long
Dim x4 As Long
Dim x5 As Long
Dim mynumbers As Variant
Dim check As Long
Dim nextrow As Long
Dim lastrow As Long
check = Range("a1").Value
mynumbers = Range("b3:p7").Value

For x1 = 1 To 11
    For x2 = 2 To 12
        For x3 = 3 To 13
            For x4 = 4 To 14
                 For x5 = 5 To 15
                              
                               If (mynumbers(1, x1) + mynumbers(2, x2) + mynumbers(3, x3) + mynumbers(4, x4) + mynumbers(5, x5)) = check Then
                                
                                If x = 65000 Then
                                   x = 0
                                   y = y + 6
                                End If
                                   x = x + 1
                                  
                                Cells(x + 2, y).Value = mynumbers(1, x1)
                                Cells(x + 2, y + 1).Value = mynumbers(2, x2)
                                Cells(x + 2, y + 2).Value = mynumbers(3, x3)
                                Cells(x + 2, y + 3).Value = mynumbers(4, x4)
                                Cells(x + 2, y + 4).Value = mynumbers(5, x5)
                               End If
                Next x5
            Next x4
        Next x3
    Next x2
Next x1


    
End Sub

Thank you all.

I am using Excel 2000

Regards,
Moti
 

Attachments

  • Create Combinations If Sum Matches.png
    Create Combinations If Sum Matches.png
    44.2 KB · Views: 9

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hello,

May be it is confusing I want explain it again in a better way may it help

To match target sum I want to VBA check all the 5 rows to pick one number from each row through the...Row 3 to Row7

1st it picks num...B3 then C4...then D5...then E6 & finally with these 4 numbers it keep adding All number one by one threw F7 to P7

If not match it goes to pick next to E5 num, which is F6, and start up adding (All number one by one threw F7 to P7).... Here is a trick it must now pick G7 to P7....

And if next pick is G6 then start up adding H7 To P7 always 1 next to previous row.... till the end all sum with the same procedure.

Thank you all.

I am using Excel 2000

Regards,
Moti
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top