URGENT- Random Sample per range of data

I am trying to create a function (?) that will allow me to get a specific
number of randomly selected cells out of a range. I know this is available,
but I can't seem to get it to do the following:

BACKGROUND- I have a spreadsheet that has employee names for the column
titles, and training module names for each row. The connecting cells
indicate the date which these training modules were completed [ex- Joe Shmoe
(column title) was training on Beancounting (row title) on 7/7/77
(coordinating cell data).]

PROBLEM- I need to randomly select 315 cells out of a range of
employees/training modules (for example, range B2-AF247). I know this can be
accomplished through the analysis tool, but I can't seem to get it to simply
HIGHLIGHT THE RANDOMLY SELECTED CELLS within the actual spreadsheet.
Instead, it seems to only pull the actual data out of the cells and throw it
somewhere else, which prevents me from having any reference as to the
employee and training module.

SOLUTION- ???

Sub ABCD()
Dim rng As Range
Dim rng1 As Range
Dim num As Long
Dim l As Long, i As Long
num = 315
Set rng = Range("B2:AF247")
rng.Interior.ColorIndex = xlNone
i = 0
Do While i < num
l = Int(Rnd() * rng.Count + 1)
Set rng1 = rng(l)
If rng1.Interior.ColorIndex = xlNone Then
rng1.Interior.ColorIndex = 6
i = i + 1
End If
Loop

End Sub


--
Regards,
Tom Ogilvy

"Another Jennifer" <Another [email protected]> wrote in
message news:[email protected]...
> I am trying to create a function (?) that will allow me to get a specific
> number of randomly selected cells out of a range. I know this is
available,
> but I can't seem to get it to do the following:
>
> BACKGROUND- I have a spreadsheet that has employee names for the column
> titles, and training module names for each row. The connecting cells
> indicate the date which these training modules were completed [ex- Joe
Shmoe
> (column title) was training on Beancounting (row title) on 7/7/77
> (coordinating cell data).]
>
> PROBLEM- I need to randomly select 315 cells out of a range of
> employees/training modules (for example, range B2-AF247). I know this can
be
> accomplished through the analysis tool, but I can't seem to get it to
simply
> HIGHLIGHT THE RANDOMLY SELECTED CELLS within the actual spreadsheet.
> Instead, it seems to only pull the actual data out of the cells and throw
it
> somewhere else, which prevents me from having any reference as to the
> employee and training module.
>
> SOLUTION- ???
>

Tom,
I've learned how to apply the process, and it works fantastically, thank
you!!! I am now wondering if there's a way to list those selected boxes (NOT
the data contained in the boxes, but the actual cell #, such as A:220) in a
separate column on a separate spreadsheet.

"Tom Ogilvy" wrote:

> Sub ABCD()
> Dim rng As Range
> Dim rng1 As Range
> Dim num As Long
> Dim l As Long, i As Long
> num = 315
> Set rng = Range("B2:AF247")
> rng.Interior.ColorIndex = xlNone
> i = 0
> Do While i < num
> l = Int(Rnd() * rng.Count + 1)
> Set rng1 = rng(l)
> If rng1.Interior.ColorIndex = xlNone Then
> rng1.Interior.ColorIndex = 6
> i = i + 1
> End If
> Loop
>
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Another Jennifer" <Another [email protected]> wrote in
> message news:[email protected]...
> > I am trying to create a function (?) that will allow me to get a specific
> > number of randomly selected cells out of a range. I know this is
> available,
> > but I can't seem to get it to do the following:
> >
> > BACKGROUND- I have a spreadsheet that has employee names for the column
> > titles, and training module names for each row. The connecting cells
> > indicate the date which these training modules were completed [ex- Joe
> Shmoe
> > (column title) was training on Beancounting (row title) on 7/7/77
> > (coordinating cell data).]
> >
> > PROBLEM- I need to randomly select 315 cells out of a range of
> > employees/training modules (for example, range B2-AF247). I know this can
> be
> > accomplished through the analysis tool, but I can't seem to get it to
> simply
> > HIGHLIGHT THE RANDOMLY SELECTED CELLS within the actual spreadsheet.
> > Instead, it seems to only pull the actual data out of the cells and throw
> it
> > somewhere else, which prevents me from having any reference as to the
> > employee and training module.
> >
> > SOLUTION- ???
> >
>
>
>

Sub ABCD()
Dim rng As Range
Dim rng1 As Range
Dim num As Long
Dim l As Long, i As Long
num = 315
Set rng = Range("B2:AF247")
rng.Interior.ColorIndex = xlNone
i = 0
Do While i < num
l = Int(Rnd() * rng.Count + 1)
Set rng1 = rng(l)
If rng1.Interior.ColorIndex = xlNone Then
rng1.Interior.ColorIndex = 6
cells(i+1,"AH").Value = rng1.Address(0,0)
i = i + 1
End If
Loop

End Sub

Should list the cells in column AH

--
Regards,
Tom Ogilvy


"Another Jennifer" <[email protected]> wrote in
message news:[email protected]...
> Tom,
> I've learned how to apply the process, and it works fantastically, thank
> you!!! I am now wondering if there's a way to list those selected boxes
(NOT
> the data contained in the boxes, but the actual cell #, such as A:220) in
a
> separate column on a separate spreadsheet.
>
> "Tom Ogilvy" wrote:
>
> > Sub ABCD()
> > Dim rng As Range
> > Dim rng1 As Range
> > Dim num As Long
> > Dim l As Long, i As Long
> > num = 315
> > Set rng = Range("B2:AF247")
> > rng.Interior.ColorIndex = xlNone
> > i = 0
> > Do While i < num
> > l = Int(Rnd() * rng.Count + 1)
> > Set rng1 = rng(l)
> > If rng1.Interior.ColorIndex = xlNone Then
> > rng1.Interior.ColorIndex = 6
> > i = i + 1
> > End If
> > Loop
> >
> > End Sub
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Another Jennifer" <Another [email protected]> wrote in
> > message news:[email protected]...
> > > I am trying to create a function (?) that will allow me to get a
specific
> > > number of randomly selected cells out of a range. I know this is
> > available,
> > > but I can't seem to get it to do the following:
> > >
> > > BACKGROUND- I have a spreadsheet that has employee names for the
column
> > > titles, and training module names for each row. The connecting cells
> > > indicate the date which these training modules were completed [ex- Joe
> > Shmoe
> > > (column title) was training on Beancounting (row title) on 7/7/77
> > > (coordinating cell data).]
> > >
> > > PROBLEM- I need to randomly select 315 cells out of a range of
> > > employees/training modules (for example, range B2-AF247). I know this
can
> > be
> > > accomplished through the analysis tool, but I can't seem to get it to
> > simply
> > > HIGHLIGHT THE RANDOMLY SELECTED CELLS within the actual spreadsheet.
> > > Instead, it seems to only pull the actual data out of the cells and
throw
> > it
> > > somewhere else, which prevents me from having any reference as to the
> > > employee and training module.
> > >
> > > SOLUTION- ???
> > >
> >
> >
> >