VBA pick random cell - print

Discussion in 'Microsoft Excel Misc' started by flashing frank, Nov 23, 2004.

  1. hi,
    i want to pick a random cell (containing text) in a given range.

    i used the "randbetween" function in excel, but i am now needing thi
    in visual basic.

    i found the following vba snipplet, but i don´t understand it, an
    can´t implement it in my code:


    PHP code
    -------------------
    With Range("B1")
    .FormulaR1C1 = _
    "=OFFSET(Sheet1!RC[-1],RANDBETWEEN(0,9),0)"

    -------------------
    it would be great if someone could give me an example of picking
    random cell in the range lets say ("A1:B10")

    i would also like to know how to combine two random picks with th
    print function.
    something like:

    Print #1, (function for random cell1 ("a1:B10")) & (function for rand
    cell2 ("C3:C10"))

    i hope i described my problem good enough...

    thank

    --
    flashing fran
    -----------------------------------------------------------------------
    flashing frank's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1375
    View this thread: http://www.excelforum.com/showthread.php?threadid=31984
     
    flashing frank, Nov 23, 2004
    #1
    1. Advertisements

  2. flashing frank

    Jim Rech Guest

    >>it would be great if someone could give me an example of picking a
    random cell in the range lets say ("A1:B10")

    Okay, here such a function:

    Function RandCell(Rg As Range) As Range
    Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)
    End Function

    and here's a test to satisfy yourself it's working. I'm sure you can hook
    RandCell into your code.

    Sub RandCellTest()
    Dim Counter As Long
    Dim TargetRg As Range
    Application.ScreenUpdating = False
    Set TargetRg = Range("A1:A10")
    TargetRg.ClearContents
    Dim Cell As Range
    For Counter = 1 To 10000
    Set Cell = RandCell(TargetRg)
    Cell.Value = Cell.Value + 1
    Next
    End Sub

    --
    Jim Rech
    Excel MVP
     
    Jim Rech, Nov 23, 2004
    #2
    1. Advertisements

  3. flashing frank, Nov 24, 2004
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    1
    Views:
    934
    Norman Jones
    Jun 27, 2005
  2. Guest

    Pick a Random name from a list.

    Guest, Jun 6, 2006, in forum: Microsoft Excel Misc
    Replies:
    8
    Views:
    291
    Dave Peterson
    Sep 30, 2008
  3. saman110 via OfficeKB.com

    Random pick of a cell

    saman110 via OfficeKB.com, Jul 31, 2007, in forum: Microsoft Excel Misc
    Replies:
    5
    Views:
    239
  4. fruitchunk

    How to random pick a cell from more than one column

    fruitchunk, Jan 6, 2009, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    293
    fruitchunk
    Jan 12, 2009
  5. Flammy

    Pick random cell in VBA

    Flammy, Apr 20, 2011, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    580
    Flammy
    Apr 20, 2011
Loading...

Share This Page