Results 1 to 3 of 3

Thread: How to display these combinations
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to display these combinations

    0 0 0 0
    1 1 1 1
    2 2 2 2
    3 3 3 3
    4 4 4 4
    5 5 5 5
    6 6 6 6
    7 7 7 7
    8 8 8 8
    9 9 9 9


    Code:

    Function ListPermut(num As Integer)
    'Permutations without repetition

    Dim c As Long, r As Long, p As Long
    Dim rng() As Long, temp As Long, i As Long
    Dim temp1 As Long, y() As Long, d As Long
    p = WorksheetFunction.Permut(num, num)

    ' Create array
    ReDim rng(1 To p, 1 To num)

    'Create first row in array (1, 2, 3, ...)
    For c = 1 To num
    rng(1, c) = c
    Next c

    For r = 2 To p
    ' 1. Find the first smaller number rng(r-1, c-1) For c = num To 1 Step -1
    If rng(r - 1, c - 1) < rng(r - 1, c) Then
    temp = c - 1
    Exit For
    End If
    Next c
    ' Copy values from previous row
    For c = num To 1 Step -1
    rng(r, c) = rng(r - 1, c)
    Next c
    ' 2. Find a larger number than rng(r-1, temp)as far to the right as possible
    For c = num To 1 Step -1
    If rng(r - 1, c) > rng(r - 1, temp) Then
    temp1 = rng(r - 1, temp)
    rng(r, temp) = rng(r - 1, c)
    rng(r, c) = temp1
    ReDim y(num - temp)
    i = 0
    For d = temp + 1 To num
    y(i) = rng(r, d)
    i = i + 1
    Next d
    i = 0
    For d = num To temp + 1 Step -1
    rng(r, d) = y(i)
    i = i + 1
    Next d
    Exit For
    End If
    Next c
    Next r

    ListPermut = rng

    End Function


    I would like for this to display all of the combinations from 0000 - 9999

    When I enter a blank cell and just enter ListPermut(4) it only returns just 1 but I would like to expand this to the entire 4 x 10 matrix. How do I invoke the UDF to display all of the numbers like this

    0000
    0001
    0002
    0003
    0004
    etc.. all the way through 9999

    Thanks in advance

  2. #2
    New Member
    Join Date
    Sep 2014
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to display these combinations

    Thanks I figured it out. I got it now.

  3. #3
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,604
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: How to display these combinations

    Wouldn't the formula =TEXT(ROW(A1),"0000") dragged down do what you want?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •