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

1. 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  Reply With Quote

2. Re: How to display these combinations

Thanks I figured it out. I got it now.  Reply With Quote

3. Re: How to display these combinations

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

User Tag List

Tags for this Thread

long, num, permutations/combinations, rngr, temp  Posting Permissions

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