title: Why is the first random number always the same?
body: <p>I'm working on a macro that selects a random series of employee id numbers for random testing.  The code I have works well except the first number returned is always the same.  For example, if my ID numbers are 1-100 and I want 10 random numbers, the first number will always be 1 and then at random after that.</p>
  
  <p>As an extra challenge, is it possible to make it where the same numbers won't be selected until the list has been cycled through?</p>
  
  <p>Here is the code that I'm using.</p>
  
  <pre><code>Sub Macro1()
     '
     '
     '
  '
   Dim CountCells
   Dim RandCount
  Dim LastRow
  Dim Counter1
  Dim Counter2
  Worksheets.Add().Name = "Sheet1"
  Worksheets("Employee ID#").Select
   Range("a2:A431").Select
  Selection.Copy
  Worksheets("Sheet1").Select
  Selection.PasteSpecial
  
  Worksheets("Sheet1").Select
  Range("A1").Select
  CountCells = WorksheetFunction.Count(Range("A:A")) 'quantity of random numbers to pick from
  If CountCells = 0 Then Exit Sub
  On Error Resume Next
  Application.DisplayAlerts = False
  RandCount = Application.InputBox(Prompt:="How many random numbers do you want?", _
        Title:="Random Numbers Selection", Type:=1)
  On Error GoTo 0
  Application.DisplayAlerts = True
  RandCount = Int(RandCount)
  If Int(RandCount) &lt;= 0 Or RandCount = False Then Exit Sub
  If RandCount &gt; CountCells Then
      MsgBox "Requested quantity of numbers is greater than quantity of available data"
      Exit Sub
  End If
  
  
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  'clear working area
  Range("B:C").ClearContents
  'clear destination area
  Range("Sheet2!A:A").ClearContents
  'create index for sort use
  Range("B1") = 1
  Range(Cells(1, 2), Cells(LastRow, 2)).DataSeries , Step:=1
  'create random numbers for sort
  Range("C1") = "=RAND()"
  Range("C1").Copy Range(Cells(1, 3), Cells(LastRow, 3))
  'randomly sort data
  Range(Cells(1, 1), Cells(LastRow, 3)).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _
      Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
  'data has been sorted randomly, cells in column A, rows 1 through the quantity desired will be chosen
  Counter1 = 1
  Counter2 = 1
  Do Until Counter1 &gt; RandCount
      If IsNumeric(Cells(Counter2, 1).Value) And Cells(Counter2, 1).Value &lt;&gt; Empty Then
          Range("Sheet2!A" &amp; Counter1) = Cells(Counter2, 1).Value
          Counter1 = Counter1 + 1
          'Selection.ClearContents
      End If
      Counter2 = Counter2 + 1
  
  Loop
  'resort data into original order and clear working area
  Range(Cells(1, 1), Cells(LastRow, 3)).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
      Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
  Range("B:C").ClearContents
  Sheets("Sheet2").Select
  'Sheets("Sheet2").PrintOut
  
  
  
  
  
  End Sub
  </code></pre>
  
  <p>Thanks in advance for any help.</p>