VBA code for combinations given a sum.

[Prit50]

Hello Friends
Using Excel, am looking for a <ACRONYM title="visual basic for applications">VBA</ACRONYM> code that will list all combinations(Max 6 numbers) that will arrive at sum 114.
Conditions are:
Column A: Number 1 to 40
Sum: 114
Number cannot repeat in any each combination.

The output is to list all combinations(6 numbers only); each number displayed in a single cell that arrive at sum 114.

Many thanks for your assistance.​

 

[Prit50]

Hello shg,
I would like to add 3 conditions to the expected results, grateful if you can help with an amended code:
1) 3 numbers should be from 1 to 20
2) 3 numbers should be from 21 to 40
3) The results (6 numbers) should have a combination of 4 even + 2 odd

Many thanks

 

[JazzyJellyB]

About 64K combinations:

Sub Prit50()
    Const iSum      As Long = 114
    Const n         As Long = 40
    Dim m           As Long
    Dim aiC()       As Long
    Dim rOut        As Range

    Set rOut = Range("A1")

    Application.ScreenUpdating = False
    For m = 1 To 6
        ReDim aiC(1 To m)
        aiC(1) = -1

        Do While bNextCombo(aiC, n)
            If WorksheetFunction.Sum(aiC) = iSum - m Then
                Set rOut = rOut.Offset(1)
                rOut.Resize(, m).Value = aiC
            End If
        Loop
    Next m

    rOut(2, 1).Value = 1
    rOut(2, 1).Copy
    Cells.SpecialCells(xlCellTypeConstants, xlNumbers).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlAdd
    Application.ScreenUpdating = True
    Beep
End Sub

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

num1​

[/td][td]

num2​

[/td][td]

num3​

[/td][td]

num4​

[/td][td]

num5​

[/td][td]

num6​

[/td][/tr]

[tr][td]

2​

[/td][td]

39​

[/td][td]

38​

[/td][td]

37​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

40​

[/td][td]

38​

[/td][td]

36​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

40​

[/td][td]

39​

[/td][td]

35​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

30​

[/td][td]

29​

[/td][td]

28​

[/td][td]

27​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

31​

[/td][td]

29​

[/td][td]

28​

[/td][td]

26​

[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

31​

[/td][td]

30​

[/td][td]

27​

[/td][td]

26​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

31​

[/td][td]

30​

[/td][td]

28​

[/td][td]

25​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

31​

[/td][td]

30​

[/td][td]

29​

[/td][td]

24​

[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

32​

[/td][td]

29​

[/td][td]

27​

[/td][td]

26​

[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

32​

[/td][td]

29​

[/td][td]

28​

[/td][td]

25​

[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

32​

[/td][td]

30​

[/td][td]

27​

[/td][td]

25​

[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

32​

[/td][td]

30​

[/td][td]

28​

[/td][td]

24​

[/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

32​

[/td][td]

30​

[/td][td]

29​

[/td][td]

23​

[/td][td][/td][td][/td][/tr]
[/table]

Hello Sgh! I am a new guy here. I hope you don't mind me hijacking this thread? I tried this code in Excel 2007 and 2013 to run this code and I get this error - Compile Error: Sub or Function not defined. In the VBA code bNextCombo is highlighted. Any ideas please?

 

[Eric W]

Welcome to the forum, JazzyJellyB!

There's no problem in adding a question to an existing thread. The only risk is that people may not see it. The regulars here often gravitate to looking at threads with 0 responses, so if an old thread gets a new message, they may not see it. It could show up on the "New Posts" list, but if people don't see it there before it ages off, then you may never get an answer.

With that said, if you look at shg's post #3 in this thread, you'll see in the code he posted there's an additional function he wrote. He didn't add it to the later code because it didn't change. So just copy the bNextCombo part of the code from post #3 and add it to your code and see if that helps.

Good luck!

 

[shg]

Thank you, Eric.

 

[JazzyJellyB]

Bingo!

Eric! Thank you for the information
SHG! Thank you for making my life just a little easier!

 

[shg]

You're welcome.