MACRO FOR COMBINATIONS !!

[jay dean]

In each cell in Range A1:M1, there is data (either a number or an
alphabet).

I need a macro to ask "How many items do you wanna combine?" When I
enter the number, the macro will list all the possible combinations of
the alphabets Or numbers I can choose from A1:M1 starting in cell A3.

Any help would be appreciated.

Thanks
Jay Dean

 

[Tom Ogilvy]

This sounds an awful lot about a previous post by someone else a while ago
even down to the answer in A3. Anyway, here it an adjustment to the answer
to that question.

Sub Combinations()
Dim n As Integer, m As Integer
Dim v As Variant, rng As Range
numcomb = 0
Set rng = Range("A1:M1")
'Set rng = rng.Resize(1, 5)
v = Application.Transpose(Application _
.Transpose(rng))
n = UBound(v, 1)
m = InputBox("Taken how many at a time?", "Combinations")
If Application.Combin(n, m) > 64530 Then
MsgBox "Too many to write out, quitting"
Exit Sub
End If
Range("A3").Select
Comb2 n, m, 1, "'", v
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
Sub Comb2(ByVal n As Integer, ByVal m As Integer, _
ByVal k As Integer, ByVal s As String, v As Variant)
Dim v1 As Variant
If m > n - k + 1 Then Exit Sub
If m = 0 Then
'Debug.Print "->" & s & "<-"
v1 = Split(Replace(Trim(s), "'", ""), " ")
For i = LBound(v1) To UBound(v1)
ActiveCell.Offset(0, i) = v(v1(i))
Next
ActiveCell.Offset(1, 0).Select
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", v
Comb2 n, m, k + 1, s, v
End Sub

will generate the combinations of which you speak.

 

[jay dean]

Hi Tom - Thanks! Your code works perfectly. I am the same guy from whom
the other post was (i.e) Jay Dean, unless there's yet some other post
you were talking about.

I cannot explain why the double-posting occurred because I think I
posted my message once, but hey, these things do happen...

Anyway, thanks again for the help. I hope this response does not post
twice...LOL!

Jay Dean.

 

[Tom Ogilvy]

this was from a guy posting as MAXI doing something with KENO as I recall.