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.
Thank you all.
I am using Excel 2000
Regards,
Moti
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.
196 | Choose Delay Patterns To Play Sum | |||||||||||||||||||||||
Partido | n1 | n2 | n3 | n4 | n5 | n6 | n7 | n8 | n9 | n10 | n11 | n12 | n13 | n14 | n15 | c1 | c2 | c3 | c4 | c5 | Note | |||
1 | 7 | 21 | 9 | 35 | 39 | 5 | 34 | 23 | 37 | 15 | 6 | 7 | 58 | 58 | 4 | 69 | NOT VALID | |||||||
2 | 58 | 19 | 1 | 38 | 34 | 9 | 65 | 6 | 17 | 49 | 3 | 7 | 58 | 25 | 77 | 29 | OK | |||||||
3 | 58 | 25 | 5 | 76 | 65 | 4 | 1 | 86 | 7 | 34 | 24 | 7 | 58 | 4 | 58 | 69 | NOT VALID | |||||||
4 | 25 | 58 | 98 | 19 | 77 | 21 | 45 | 76 | 2 | 4 | 3 | 7 | 58 | 4 | 98 | 29 | NOT VALID | |||||||
5 | 98 | 19 | 35 | 69 | 78 | 33 | 2 | 29 | 17 | 96 | 6 | 7 | 58 | 34 | 19 | 78 | NOT VALID | |||||||
7 | 19 | 58 | 77 | 35 | NOT VALID | |||||||||||||||||||
7 | 19 | 25 | 76 | 69 | NOT VALID | |||||||||||||||||||
7 | 19 | 76 | 25 | 69 | NOT VALID | |||||||||||||||||||
7 | 19 | 76 | 77 | 17 | OK | |||||||||||||||||||
7 | 19 | 65 | 76 | 29 | OK | |||||||||||||||||||
7 | 19 | 34 | 58 | 78 | NOT VALID | |||||||||||||||||||
7 | 19 | 24 | 77 | 69 | NOT VALID | |||||||||||||||||||
7 | 1 | 76 | 77 | 35 | NOT VALID | |||||||||||||||||||
7 | 1 | 65 | 25 | 98 | NOT VALID | |||||||||||||||||||
7 | 1 | 65 | 45 | 78 | NOT VALID | |||||||||||||||||||
7 | 1 | 86 | 4 | 98 | NOT VALID | |||||||||||||||||||
7 | 1 | 34 | 58 | 96 | NOT VALID | |||||||||||||||||||
7 | 1 | 34 | 76 | 78 | NOT VALID | |||||||||||||||||||
7 | 38 | 58 | 58 | 35 | NOT VALID | |||||||||||||||||||
7 | 38 | 58 | 76 | 17 | NOT VALID | |||||||||||||||||||
7 | 38 | 5 | 77 | 69 | NOT VALID | |||||||||||||||||||
7 | 38 | 76 | 58 | 17 | NOT VALID | |||||||||||||||||||
7 | 38 | 34 | 98 | 19 | NOT VALID | |||||||||||||||||||
7 | 38 | 34 | 19 | 98 | NOT VALID | |||||||||||||||||||
7 | 38 | 34 | 21 | 96 | NOT VALID | |||||||||||||||||||
7 | 38 | 24 | 58 | 69 | NOT VALID | |||||||||||||||||||
7 | 38 | 24 | 98 | 29 | NOT VALID | |||||||||||||||||||
7 | 34 | 58 | 19 | 78 | NOT VALID | |||||||||||||||||||
7 | 34 | 76 | 77 | 2 | NOT 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