Results 1 to 3 of 3

Validation of 3 Unique Random selections

Threaded View

  1. #1
    Registered User
    Join Date
    05-22-2014
    Posts
    16

    Validation of 3 Unique Random selections

    From an indexed list I need to select 3 unique names randomly. The index number is generated by a cell within the spreadsheet (Cell A1: =RANDBETWEEN(1,26) ); in actual application the “26” is not static.

    The macro searches for the index number and records both it and the name to which it is indexed. The Selection and “?????” columns are there more for my verification except that, if the line: Range("G" & 4 + c).Value = Range("A1").Value is commented, after a time of repeatedly pressing the button the macro ‘freezes’ and Ctrl/Break produces a “91” error.

    That is my first issue.
    My second is: If my data does not begin in the second row (or my search does not begin from cell “B1”) then index 1 is seemingly never selected. Why?

    I am confident that the macro will always generate three names successfully, but I need some assurance that my propositions about my two issues are correct.
    That said, if there is a simple way, consistent with my given macro, that overcomes both issues then I’d be willing to be advised. Please.
    Triple.jpg
    Here is my macro:

    Sub triple()
    Range("E1").Select
    Dim str As String
    
    For c = 1 To 3
        Do
        n = 0
        str = Range("A1").Value
        Range("G" & 4 + c).Value = Range("A1").Value  'Why this line?
        'Find str
        Range("B1:B27").Find(What:=str, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
        Do Until ActiveCell.Value <> ActiveCell.Offset(1, 0).Value
        Cells.FindNext(After:=ActiveCell).Activate
        Loop
    
        For m = 1 To c - 1
            If ActiveCell.Value = Range("D" & 4 + m) Then
                n = n + 1
            End If
        Next
        Loop While n > 0
    
        ActiveCell.Resize(1, 2).Copy
        Range("D" & 4 + c).Select
        ActiveSheet.Paste
        Range("F" & 4 + c).Value = c
    Next
    End Sub
    Last edited by BrianCob; 05-22-2014 at 03:22 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with random selections
    By hrts4him in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2012, 04:08 PM
  2. Unique, random selections from one sheet to another
    By Hoosaskin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-01-2011, 12:58 PM
  3. Multiple selections from a pick list - only unique selections (no repeats) ?
    By opsayo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2011, 06:25 PM
  4. Multiple Random Selections
    By Xtopher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2011, 09:31 PM
  5. Random Selections
    By StIves in forum Excel General
    Replies: 4
    Last Post: 03-03-2009, 09:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1