Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO FOR COMBINATIONS !!
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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO FOR COMBINATIONS !!
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. -- Regards, Tom Ogilvy "jay dean" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO FOR COMBINATIONS !!
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. *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO FOR COMBINATIONS !!
this was from a guy posting as MAXI doing something with KENO as I recall.
-- Regards, Tom Ogilvy "jay dean" wrote in message ... 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. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a macro in excel so that it can generate a list ofunique records using all permutations and combinations of the data in eachrow ad column | Excel Discussion (Misc queries) | |||
all possible combinations. | Excel Discussion (Misc queries) | |||
Macro/Function for Keyboard button combinations | Excel Programming | |||
Macro to add numbers to create combinations that equal certain amo | Excel Discussion (Misc queries) | |||
All combinations | Excel Programming |