VBA Random selection from list of names

wayne0881

Board Regular
Joined
Nov 2, 2009
Messages
95
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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?
 
Upvote 0
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:
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top