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

[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()?

 

[Bob Phillips]

Is this any good?

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Ken Johnson]

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

 

[Guest]

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


“kckarâ€ç¼–写：

 

[nsv]

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.

 

[bplumhoff]

Hello,

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

Regards,
bernd