Hello everyone
I have this code that calculates all the possible combinations for the sum of specific numbers
Option Explicit
Sub PossibleCombinations()
Dim r As Variant
Dim v As Double
Dim i As Long
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim f As Long
Const x As Long = 20
ReDim r(1 To 1048576, 1 To 13)
Application.ScreenUpdating = False
For a = 1 To x
For b = 1 To x
For c = 1 To x
For d = 1 To x
For e = 1 To x
For f = 1 To x
v = Application.WorksheetFunction.Sum(a * 49, b * 99, c * 149, d * 199, e * 224, f * 249)
If v = 14221 Then
i = i + 1
r(i, 1) = a
r(i, 2) = b
r(i, 3) = c
r(i, 4) = d
r(i, 5) = e
r(i, 6) = f
r(i, 7) = a * 49
r(i, 8) = b * 99
r(i, 9) = c * 149
r(i, 10) = d * 199
r(i, 11) = e * 224
r(i, 12) = f * 249
r(i, 13) = Application.WorksheetFunction.Sum(r(i, 7), r(i, 8), r(i, 9), r(i, 10), r(i, 11), r(i, 12))
If i >= Rows.Count Then GoTo Skipper
End If
Next f
Next e
Next d
Next c
Next b
Next a
Skipper:
Range("A1").Resize(i, UBound(r, 2)).Value = r
Application.ScreenUpdating = True
MsgBox "Done...", 64
End Sub
It is working well but it took about 6 minutes (Is there a way to make it faster?)
Thanks advanced for help
Bookmarks