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

URGENT- Random Sample per range of data
 

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 wrote in
message ...
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- ???

URGENT- Random Sample per range of data
 

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 wrote in
message ...
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- ???

URGENT- Random Sample per range of data
 

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" wrote in
message ...
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 wrote in
message ...
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- ???