Forum Discussion

yugal40's avatar
yugal40
Brass Contributor
Nov 19, 2021
Solved

permutation and combination

Hi Folks, Hope you all are doing great! i have numbers in the column a, Now i want to calculate all the permutation and combination of all the pair of 3 cells cells and there should be no repetit...
excel
Excel for web
Formulas and Functions
Macros and VBA
Office Scripts
  • HansVogelaar's avatar
    HansVogelaar
    Nov 20, 2021

    yugal40 

    Change the Test macro as follows; the rest can remain the same:

    Sub Test()
    Dim Num As Long
    Dim r As Long
    Dim LastRow As Long
    Num = Application.InputBox(Prompt:="How many items in each combination?", Default:=3, Type:=1)
    If Num < 2 Then
        Beep
        Exit Sub
    End If
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.count).End(xlUp).Row
    ReDim myArray(1 To LastRow)
    For r = 1 To LastRow
        myArray(r) = Range("A" & r).Value
    Next r
    Call CombosNoRep(myArray, Num)
    Application.ScreenUpdating = True
End Sub
yugal40's avatar
yugal40
Brass Contributor
Nov 20, 2021
I really appreciate your understanding of macros!

Really thanks for your reply, It worked for me.

Just one more thing can we give an input instead of hard coding 3 as a number?

It will make my task more easier

Thanks again,
Have a great day.
HansVogelaar's avatar
HansVogelaar
MVP
Nov 20, 2021

yugal40 

Change the Test macro as follows; the rest can remain the same:

Sub Test()
    Dim Num As Long
    Dim r As Long
    Dim LastRow As Long
    Num = Application.InputBox(Prompt:="How many items in each combination?", Default:=3, Type:=1)
    If Num < 2 Then
        Beep
        Exit Sub
    End If
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.count).End(xlUp).Row
    ReDim myArray(1 To LastRow)
    For r = 1 To LastRow
        myArray(r) = Range("A" & r).Value
    Next r
    Call CombosNoRep(myArray, Num)
    Application.ScreenUpdating = True
End Sub

Resources