Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combinations (Excel 2K/XP)

    A "academic" question on your line of code:
    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. #4
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combinations (Excel 2K/XP)

    Many thanks, Jan Karel.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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