using 1 through 9...equal 23

R

rsaint35

Trying to figure out how many combinations there are using the numbers 1 through 9 4 inputs to equal 23 without repeating the combination.

Example:

9+9+4+1=23
9+9+3+2=23
and so on...there has to be a simple way to figure this out besides writing it out.

Thanks for any help
 
B

Ben McClave

Rich,

I'm not sure from your post whether the order of numbers is important in the equation (i.e. is 9+9+4+1 the same as 1+4+9+9?). The following macro will list each combination of 4 numbers adding to 23 in columns A:D of the active sheet. It will prompt you for whether order of numbers is important. It's probably not a very efficient macro, but it works for me.

Incidentally, the macro calculates 420 combinations when order is importantand 28 when it is not.

Hope this helps.

Ben

Sub CountTo23()
Dim a As Long, b As Long
Dim c As Long, d As Long
Dim Counter As Long
Dim i As Long

Counter = 1
Application.ScreenUpdating = False

For a = 1 To 9
For b = 1 To 9
For c = 1 To 9
For d = 1 To 9
If a + b + c + d = 23 Then
Range("A" & Counter).Value = a
Range("B" & Counter).Value = b
Range("C" & Counter).Value = c
Range("D" & Counter).Value = d
Counter = Counter + 1
End If
Next d
Next c
Next b
Next a

If MsgBox("Is the order of numbers important?" & vbCr & vbCr & _
"(i.e. is 9+9+4+1 distinct from 9+9+1+4?)", vbYesNo + vbQuestion, "Doesorder matter?") _
= vbYes Then GoTo 100

For i = 1 To Counter - 1
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A" & i & ":D" & i), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A" & i & ":D" & i)
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Next i

100: ActiveSheet.Range("$A$1:$D$" & Counter - 1).RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlNo
Range("A1").Activate
Application.ScreenUpdating = True

MsgBox "There are " & Range("A1").End(xlDown).Row & " combinations adding up to 23."

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top