random selection of rows
in [Programming]
|
Prev: Help with printing rows and/or columns
Next: Sheet and workbook code: All sheets should have row 1 to 5 of Shee
From: Bradly on 23 Mar 2010 21:37 I have a master list of all of our cases--there are approximately 20000 rows, each row representing a single case, and each row has 13 columns of data. I filtered the list, for example, to see all of the active cases for a case manager. Suppose that filter produces 117 cases (there are of course a different number for each case manager). I want to create a macro or use a formula to randomly select one of these rows as a way to randomly select cases for audits. Is there a way to do this? Thanks.
From: OssieMac on 24 Mar 2010 02:28 Hi Bradley, The following code makes a random selection from the filtered (visible) data and copies the row of data to Sheet2. If you change the filter and run it again then the next row of data on sheet2 is placed under the previous row. I have not included headers on sheet2 but I assume you will copy the headers from Sheet1. It is possible to run the code in a loop and automatically setting the filters to each of the Managers in turn and creating the output on Sheet2. If you want it done that way then let me know which column contains the changing filters. I have given you a code option between the asterisk lines of just selecting the row instead of copying to Sheet2. Just comment out the Copy and Paste code and uncomment the select line. Note that a space and underscore at the end of a line is a line break in an otherwise single line of code. Sub RandomSelection() 'RandBetween function in Code 'requires Analysis ToolPak AddIn 'to be loaded. 'See Help for how to load 'Analysis ToolPak. Dim rngCol As Range Dim lngCells As Long Dim lngRandom As Long Dim cel As Range Dim i As Long 'Edit "Sheet1" to your sheet name With Sheets("Sheet1") If .FilterMode Then With .AutoFilter.Range Set rngCol = .Columns(1) _ .Offset(1, 0) _ .Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) End With Else MsgBox "Filters not set." & vbLf & _ "Processing terminated." Exit Sub End If End With lngCells = rngCol.Cells.Count lngRandom = WorksheetFunction _ .RandBetween(1, lngCells) i = 0 For Each cel In rngCol i = i + 1 If i = lngRandom Then 'Copy row of data and Paste to Sheet2 'Edit "Sheet2" to your output sheet name. Range(cel, cel.Offset(0, 12)).Copy _ Destination:=Sheets("Sheet2") _ .Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0) '************************************ 'Alternative to Copy and Paste above 'Just select data. 'Range(cel, cel.Offset(0, 12)).Select '************************************ Exit For End If Next cel End Sub -- Regards, OssieMac
From: Bradly on 24 Mar 2010 14:48 I tried the code and added the Analysis ToolPak as you asked, but I get this error message: "Run-time error '438': Object doesn't support this property or method" and the following code is highlighted: lngRandom = WorksheetFunction _ .RandBetween(1, lngCells) Have I typed something in wrong? "OssieMac" wrote: > Hi Bradley, > > The following code makes a random selection from the filtered (visible) data > and copies the row of data to Sheet2. If you change the filter and run it > again then the next row of data on sheet2 is placed under the previous row. I > have not included headers on sheet2 but I assume you will copy the headers > from Sheet1. > > It is possible to run the code in a loop and automatically setting the > filters to each of the Managers in turn and creating the output on Sheet2. If > you want it done that way then let me know which column contains the changing > filters. > > I have given you a code option between the asterisk lines of just selecting > the row instead of copying to Sheet2. Just comment out the Copy and Paste > code and uncomment the select line. > > Note that a space and underscore at the end of a line is a line break in an > otherwise single line of code. > > Sub RandomSelection() > > 'RandBetween function in Code > 'requires Analysis ToolPak AddIn > 'to be loaded. > > 'See Help for how to load > 'Analysis ToolPak. > > Dim rngCol As Range > Dim lngCells As Long > Dim lngRandom As Long > Dim cel As Range > Dim i As Long > 'Edit "Sheet1" to your sheet name > With Sheets("Sheet1") > If .FilterMode Then > With .AutoFilter.Range > Set rngCol = .Columns(1) _ > .Offset(1, 0) _ > .Resize(.Rows.Count - 1, 1) _ > .SpecialCells(xlCellTypeVisible) > End With > Else > MsgBox "Filters not set." & vbLf & _ > "Processing terminated." > Exit Sub > End If > End With > > lngCells = rngCol.Cells.Count > lngRandom = WorksheetFunction _ > .RandBetween(1, lngCells) > > i = 0 > For Each cel In rngCol > i = i + 1 > If i = lngRandom Then > > 'Copy row of data and Paste to Sheet2 > 'Edit "Sheet2" to your output sheet name. > Range(cel, cel.Offset(0, 12)).Copy _ > Destination:=Sheets("Sheet2") _ > .Cells(Rows.Count, 1) _ > .End(xlUp).Offset(1, 0) > > '************************************ > 'Alternative to Copy and Paste above > 'Just select data. > 'Range(cel, cel.Offset(0, 12)).Select > '************************************ > Exit For > End If > Next cel > > End Sub > > -- > Regards, > > OssieMac > >
From: OssieMac on 24 Mar 2010 16:01 Hi Bradley, There is Analysis ToolPak and Analysis ToolPak - VBA. I think you need both. However, if still won't work then replace the following code lngRandom = WorksheetFunction _ .RandBetween(1, lngCells) with these 2 lines of code Randomize lngRandom = Int((lngCells * Rnd) + 1) -- Regards, OssieMac
From: Chip Pearson on 25 Mar 2010 10:32
>There is Analysis ToolPak and Analysis ToolPak - VBA. I think you need both. >However, if still won't work then replace the following code Because RandBetween is not a native Excel function (in 2003 and earlier), it is not going to be found under WorksheetFunction. If you have the ATP VBA reference (you don't need both ATP references, just the VBA one), you can call RandBetween as if it were a native VBA function. Note that you must have the add-in loaded and your project must reference the atpvbaen.xla library. L = RandBetween(1, 100) When I use functions from another library, I like to qualify the name of the function with the library name, just to keep things clear and well documented. L = [atpvbaen.xls].RandBetween(1, 100) The [ ] chars are required because the library name contains a period (and yes, the referenced library is "xls" not "xla"). Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Wed, 24 Mar 2010 13:01:02 -0700, OssieMac <OssieMac(a)discussions.microsoft.com> wrote: >Hi Bradley, > >There is Analysis ToolPak and Analysis ToolPak - VBA. I think you need both. >However, if still won't work then replace the following code > >lngRandom = WorksheetFunction _ > .RandBetween(1, lngCells) > >with these 2 lines of code > >Randomize >lngRandom = Int((lngCells * Rnd) + 1) |