is there a way to use rand() to select a cell

is there a way to use the rand function to select a random cell?

so if i want a random cell in column a i would put like A(rand()??

Is this any good?

=INDIRECT("A"&RANDBETWEEN(1,1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"kckar" <[email protected]> wrote in
message news:[email protected]...
>
> is there a way to use the rand function to select a random cell?
>
> so if i want a random cell in column a i would put like A(rand()??
>
>
> --
> kckar
> ------------------------------------------------------------------------
> kckar's Profile:
http://www.excelforum.com/member.php...o&userid=25322
> View this thread: http://www.excelforum.com/showthread...hreadid=513085
>

Hi kckar,
The only way I know of is to use a macro.
The following event procedure will select a random cell in the range
A1:A30 when you select B1...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Dim iRow As Long
iRow = Int(Rnd * 29 + 1)
Cells(iRow, 1).Select
End Sub

To get the code in place...
1. Copy the 6 code lines
2. Right click the sheet tab of the applicable worksheet
3. Select View code from the contextual pop up menu to enter the
appropriate part of the VBA Editor
4. Paste the code into the white space (worksheet code module)
5. File --> Save
6. Alt + F11 to return to the workbook.

The security level must be no higher than Medium for the macro to work,
and Enable Macros must be clicked on the Security Warning dialog when
opened. Tools --> Macro --> Security to change security if it is too
high, close --> open to apply new security level.

Ken Johnson

is it all right?
=INDIRECT("A"&INT((RAND()*100)))


“kckar”编写：

>
> is there a way to use the rand function to select a random cell?
>
> so if i want a random cell in column a i would put like A(rand()??
>
>
> --
> kckar
> ------------------------------------------------------------------------
> kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322
> View this thread: http://www.excelforum.com/showthread...hreadid=513085
>
>

I imagine you want the contents of that randomly chosen cell rather than just the cell number:
=INDIRECT(ADDRESS(RAND()*65536;RAND()*256))

This gives the contents of any random cell on the sheet. Press F9 to choose another. Limit the area in which you pick your cells by using figures less than 65536 (number of rows) and 256 (number of columns).

The ADDRESS-function uses only the integer part of the number, so INT is not necessary.

Hello,

I think you meant
=INDIRECT(ADDRESS(1+RAND()*65536,1+RAND()*256))

Regards,
bernd