VBA Random selection from list of names

wayne0881

Board Regular
Joined
Nov 2, 2009
Messages
87
Hi All, I'm working on a spreadsheet that I would like to pick a random name from a list of 10 names using a vba button.

the list of names are in cells C3:C12 and I would like the selected name to appear in cell D3

Thanks in advance.
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
435
Code:
Sub Test()
    Dim rw As Long
    rw = [RandBetween(3,12)]
    Cells(3, 4) = Cells(rw, 3)
End Sub
 

wayne0881

Board Regular
Joined
Nov 2, 2009
Messages
87
nice! seems to be working fine - can you explain how it works so I can add it to my vba lists. Cells 3,4 part??

Cells rw, 3 is row 3 so C is that right?
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
435
Dim rw As Long

Declare rw as a variable number (between -2,147,483,648 and 2,147,483,648) to represent the row number.

Aside: You may come across something like this:Dim rw, x, i, j, lr as Long
Be aware, that only declares lr as Long, the others are declared as Variant.

rw = [RandBetween(3,12)]

Assign rw to a random number between 3 and 12 (the row numbers of your names). It is enclosed in square brackets because it is a worksheet function, not VBA.

Cells(3, 4) = Cells(rw, 3)

Cells(3 (3rd row), 4 (4th column)) is the same as Range("D3") - where you want your random name.
= Cells(rw
(the random number generated above - ie the random row number), 3 (the 3rd column - ie the name)) is the same as Range("C" & rw) - the randomly picked name.So, in conclusion this code:

Code:
    Dim rw As Long
    rw = [RandBetween(3,12)]
    Range("D3") = Range("C" & rw)
would also work.

The main advantage of using Cells(a, b) is that you are only dealing with numbers, this lends the addressing cells within loops (especially columns) much easier... as long as you remember the row comes before the column ;)

Hope that makes Cell addressing a bit clearer.

Phew, that took 10 times longer than the code!! ;)

Edit:
Cells rw, 3 is row 3 so C is that right?
Wrong, 3 is column 3 which is C
 
Last edited:

wayne0881

Board Regular
Joined
Nov 2, 2009
Messages
87
Thank You! yes i thought it may take longer to explain than write the code ha!

(Cells rw, 3 is row 3 so C is that right? Wrong, 3 is column 3 which is C) - I meant to say Column - honest :)
 

Forum statistics

Threads
1,078,134
Messages
5,338,430
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top