1. ## Combinations (Excel 2K/XP)

A security lock uses number buttons as a key. There are 5 buttons numbered 1-5. The key combination uses 3 numbers. Would some kind soul provide either code or formula which would generate a list of all combinations.

2. ## Re: Combinations (Excel 2K/XP)

Here is one quick and dirty way:

<pre>Sub Combin()
Dim iCount1 As Integer
Dim iCount2 As Integer
Dim iCount3 As Integer
Dim sCode As String
Dim sCode1 As String
Dim sCode2 As String
For iCount1 = 1 To 5
sCode = CStr(iCount1)
For iCount2 = 1 To 5
sCode1 = sCode & CStr(iCount2)
For iCount3 = 1 To 5
sCode2 = sCode1 & CStr(iCount3)
ThisWorkbook.Worksheets("sheet1").Range("A65536"). End(xlUp).Offset(1) = sCode2
Next
Next
Next
End Sub
</pre>

3. ## Re: Combinations (Excel 2K/XP)

ThisWorkbook.Worksheets("sheet1").Range("A65536"). End(xlUp).Offset(1) = sCode2

Usually I would use the combination
ThisWorkbook.Worksheets("sheet1").cells(lRow, 1) = sCode2
lRow = lRow + 1
to accomplish this.

I know they do the same thing, but yours uses one less variable and uses 1 less line of code (3 if you also count the DIM and the initial value statement). Is there any major difference in execution (ie with LARGE datasets) of the one versus the other or is it purely a difference in preference.

Steve

4. ## Re: Combinations (Excel 2K/XP)

Many thanks, Jan Karel.

5. ## Re: Combinations (Excel 2K/XP)

Actually, I wouldn't know. I just used this and didn't give it a second thought <g>.

One could test it on a larger set of data and compare the time needed.

#### Posting Permissions

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