random selection of rows
 

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.

random selection of rows
 

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

random selection of rows
 

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
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)

random selection of rows
 

Thanks Chip. I've now tested in xl2002. I was not aware that it did not work
in earlier versions of xl.

To Bradly,

Just to make it clear what you need to do.
In the Worksheet you need the Add-In Analysis ToolPak - VBA.
In the VBA Editor select menu item Tools - References and check the box
against atpvbean.xls. (Ensure you check the box; not just select the line)
and then OK.

Then your code is as follows.

lngRandom = [atpvbaen.xls] _
.RandBetween(1, lngCells)

or you can leave out [atpvbaen.xls]. as follows. (However, I do like Chip's
suggestion to include it because it provides documentation.)

lngRandom = RandBetween(1, lngCells)


or you can use the alternative code I gave you as follows and you then do
not need Analysis ToolPak or the Reference in VBA. Probably the better
solution.

Randomize
lngRandom = Int((lngCells * Rnd) + 1)


--
Regards,

OssieMac


"Chip Pearson" wrote:

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
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)
.