VBA Random selection from list of names

[wayne0881]

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]

Sub Test()
    Dim rw As Long
    rw = [RandBetween(3,12)]
    Cells(3, 4) = Cells(rw, 3)
End Sub

 

[wayne0881]

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]

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:

    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

 

[wayne0881]

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

 

[Paul Ked]

I'll believe you, thousands wouldn't