Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Select Random cell (Office 97)

    Hi

    Is there a way to select a random cell from a range, similar to Rand() for a number

    ie column B all cells contain names and I want to pick a cell a random

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Random cell (Office 97)

    You can use something like
    =INDEX(B:B,RANDBETWEEN(1,100),1)

    You will need to have the analysis toolpak installed, if it is not installed you will get a #NAME error.
    The RANDBETWEEN(1,100) function is used to select the row number, in this case data starts in row 1 and goes to row 100.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Select Random cell (Office 97)

    Hi Tony

    Thanks for that, could I be cheeky and ask if this can be attached to a button so all users can access it without having to give to much explanation other than press the button

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Random cell (Office 97)

    Hi Braddy

    A "press the button" option will require a VBA macro and I don't have time at the moment as I have to go to work, hopefully another lounger will reply soon.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Select Random cell (Office 97)

    Hi Tony

    OK Thanks for your help.

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Random cell (Office 97)

    A formula such as provided by Tony will automatically refresh itself at each recalculation of the spreadsheet. Users can force a recalculation by pressing F9 - no VBA code needed. (You could put an instruction in a cell on the spreadsheet itself, or in a comment.)

    Here is a modified version of the formula that automatically takes the used range in column B into account:

    =INDEX(OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$[img]/forums/images/smilies/cool.gif[/img],1),INT(RAND()*COUNTA($B:$[img]/forums/images/smilies/cool.gif[/img]+1))

    This version doesn't need the Analysis Toolpak add-in. I have attached a simple worksheet with this formula (and a comment.)

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Random cell (Office 97)

    This added to a button will "goto" a random cell in the range A1:A100 (change as desired)

    <pre>Sub RandSelection()
    Dim rng As Range
    Set rng = Range("A1:A100")
    rng.Cells(Int(rng.Count * Rnd + 1)).Select
    End Sub</pre>


    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Select Random cell (Office 97)

    Hi Hans

    Thank you very much for your reply

    Regards and thanks to all who replied.

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Random cell (Office 97)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>You forgot the code

    Steve

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random cell (Office 97)

    This modification to Steve's code will automatically adjust for the number of cells containing data in column A.

    <pre>Sub RandSelection()
    Dim rng As Range
    Set rng = Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A65536").End(xlUp))
    rng.Cells(Int(rng.Count * Rnd + 1)).Select
    End Sub
    </pre>

    Legare Coleman

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random cell (Office 97)

    OOPS. Thanks, it should be t here now.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •