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
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 & "
" & i), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A" & i & "
" & 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
