mklindquist
10-22-2013, 08:47 AM
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
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