Random Selection in Excel using VBA

[mklindquist]

I have the following VBA used to select a random selection of data on a spreadsheet. I was hoping to get the results displayed in a column that the user specifies but it doesn't seem to be working. What I'm trying to do is open a worksheet with data and then go to the Macro page and hit Run of an Open Worksheet (which has the macro). So I'm thinking I have to somehow (even though I'm on the worksheet with the data) make the macro run on that page. Is this possible?

Sub RandomStudentIDs()
Dim r As Range, i As Long
Dim myNum


myNum = Application.InputBox("Enter the desired number of random data rows")
myColumn = Application.InputBox("Enter the column to select the random data from")
myResults = Application.InputBox("Enter the column to put the random data results in")

myRange = "" & myColumn & "3"
With CreateObject("System.Collections.SortedList")
Randomize
For Each r In Range(myRange, Range(myColumn & Rows.Count).End(xlUp))
.Item(Rnd) = Array(r(, 0).Value, r.Value)
Next
Columns("i").ClearContents
For i = 0 To Application.Min(myNum - 1, .Count - 1)
Cells(i + 3, "" & myResults & "").Resize(, 2).Value = .GetByIndex(i)
Next
End With
End Sub

[mklindquist]

I can randomly select data depending on the column that the user enters on the InputBox that comes up. What I really want to do is instead of the user copying and pasting the data to select the random data from to the sheet with the macro is to be able to just open the sheet with the macro and run the macro from the other workbook which I know how to do by going to Macro and selecting from All Open Workbooks. What I can't get to work is for it to run on the active workbook.

Here is my code:

Sub RandomStudentIDs()
Dim r As Range, i As Long
Dim myNum

myNum = Application.InputBox("Enter the desired number of random data rows")
myColumn = Application.InputBox("Enter the column to select the random data from")
myResults = Application.InputBox("Enter the column to put the random data results in")

ActiveWorkbook.Activate

myRange = "" & myColumn & "3"
With CreateObject("System.Collections.SortedList")
Randomize
For Each r In Range(myRange, Range(myColumn & Rows.Count).End(xlUp))
.Item(Rnd) = Array(r(, 0).Value, r.Value)
Next
Columns("i").ClearContents
For i = 0 To Application.Min(myNum - 1, .Count - 1)
Cells(i + 3, "" & myResults & "").Resize(, 2).Value = .GetByIndex(i)
Next
End With
End Sub

[p45cal]

I suspect you may have put this code in a sheet's code module rather than a standard module, since it works here on the active sheet of whichever workbook with the code in a standard module.

[Kenneth Hobs]

Welcome to the forum! Please use code tags when you post code.

See if this helps:

Sub ken()
  Dim myRange As Range
  Set myRange = Application.InputBox(prompt:="Select a Range", Type:=8)
  Debug.Print myRange.Address, myRange.Parent.Name
End Sub

[mklindquist]

Yes I have put it in the sheet's code module. Do you think I should put it in a standard module?

[Kenneth Hobs]

Yes.

[SamT]

I have merged these doubled posted threads into one.

Please take the time to read the Forum FAQs link in my signature.

Thank you.

[snb]

If the data are in workbook "students.xlsm", in sheet "sheet1" in cells "Q10:Q24" :

Sub RandomStudentIDs()
   With workbooks("students.xlsm").sheets("sheet1").Cells(10, 17).CurrentRegion.Offset(, 1)
     .Value = "=rand()"
     .Value = Evaluate("index(rank(" & .Address(0, 0) & "," & .Address(0, 0) & "),)")
    For Each cl In .Cells
       cl.Value = Cells(9 + cl.Value, 17)
    Next
   End With
End Sub