List permutations

[McAllan]

Hi again.
If I use the function =PERMUT(8,2) I get an answer 56. Is there any way to list these 56 possible permutations?
Thanks a Million.

 

[VoG]

When I tested it previously (post #4) that was with Excel 2010. Sorry but I don't know why it is erroring (make sure that you use the code from post #4).

 

[Scarecrowed]

Ok, I copied the code from post #4 and tried it...
now I got a " Run-time error '6': overflow"
and the debugger highlight :"
Sub ListPermutations()
Dim Rng As Range
Dim PopSize As Integer
Dim SetSize As Integer
Dim Which As String
Dim N As Double
Const BufferSize As Long = 4096
Set Rng = Selection.Columns(1).Cells
If Rng.Cells.Count = 1 Then
Set Rng = Range(Rng, Rng.End(xlDown))
End If
PopSize = Rng.Cells.Count - 2

 

[VoG]

Try adding the line in red. What is the message?

  If Rng.Cells.Count = 1 Then
    Set Rng = Range(Rng, Rng.End(xlDown))
  End If
  MsgBox Rng.Address
  PopSize = Rng.Cells.Count - 2

 

[Scarecrowed]

That works now, Thank you VoG

Could it be possible to have the permutations/combinations in different cells instead of in the same cells ?

 

[VoG]

Could it be possible to have the permutations/combinations in different cells instead of in the same cells ?

Try running this with the result sheet selected

Sub SplitEm()
Dim LR As Long, i As Long, X
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    X = Split(Range("A" & i).Value, ", ")
    Range("B" & i).Resize(, UBound(X) + 1).Value = X
Next i
Columns("A").Delete
End Sub

 

[Scarecrowed]

Wonderfull, thanks a lot....

 

[pella88]

Hello guys (especially VoG),

I have used code from post #4 to make combinations, and when I put around 98 items in table for combinations, i get error:

"Run-time error 1004
Unable to get the transpose property of the worksheet function class".

When I do the debug, this part of the code is highlighted:

Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
= Application.WorksheetFunction.Transpose(Buffer())
RowNum = RowNum + BufferPtr

This error appeared two times and when I deleted this part of the code, macro wouldn't run at all (which makes sense), so I put it back, and now the macro works. Dunno what I did, but if you have any idea why the error happens, can you please share, in case it happens again.

Best regards,