excel macro to randomly select a cell from A1-A?

Question

2.46/5 (3 votes)

See more:

Im wanting to select a cell from a list of names. they occupy the column A but an unknown number of rows. i want to have a macro, which assigned to a button which i click to get a "winner" randomly.

thanks in advance

Posted 16-Dec-13 13:19pm

nebiam

Add a Solution

Comments

Peter Leow 16-Dec-13 19:52pm

What have you tried?

nebiam 16-Dec-13 20:44pm

i have tried a number of things, infact i have created a webb app to read directly from the db and generate a random name via C# asp.net but excel was never my forte. this is going to be used as a backup. basically in excel, i tired things like =INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1) which give me the correct answer, but i wanted to assign it to a macro, and vb is not my cup of tea. any help would be great.

Sergey Alexandrovich Kryukov 16-Dec-13 20:20pm

I would say this is remotely relevant to "real" software development, almost off-topic. Anyway, what have you tried so far?
—SA

Homero Rivera 16-Dec-13 22:45pm

Disagree. I think Excel Macros should be nursery school for developers because the results tend to be so visible... You can create so many cool things such as custom EA Sports FIFA tournaments, schedule people on a call center (done that), compute wages based on attendance (done that), help your accounting department, etc...

Not trying to be mean, Comrade, just I think Excel Macros are a light-weight solution to a lot of problems. You don't use .50 caliber gun to hunt a dove do you? Well, I think Excel Macros are for doves.

Sergey Alexandrovich Kryukov 16-Dec-13 22:57pm

Perhaps you did not understand my point. I am not trying to question the value of spreadsheets; I use them myself sometimes. Despite of their values, the spreadsheets don't really produce software.

I just think this is a kind of programming at the level of the regular system users, not software developers. I know many people who can do Excel calculation but have no programming skills at all. So, this is just a matter of what this site should cover. I think that it should still be devoted to computer science and software development, not anything below this level.

This is just my own opinion, nothing else.

—SA

Sergey Alexandrovich Kryukov 16-Dec-13 23:02pm

Ah, now I see your point. You mean VBA development in Excel. This is something different, I did not really realize that the problem is about VBA programming. Well, yes, this is close to software development...
—SA

Maciej Los 17-Dec-13 2:04am

Virus?

2 solutions

Solution 2

I would say: maybe Solution 1 is working, but it's poor, sorry.

I'll try to explain why...

Code has got no context

VB

Range("A1").Select

Dynamically Set Range in VBA for FillDown

^

Using Select is really bad programming practice, unless it is intended effect.
Unnecessary loops slow-down execution of code

VB

Do While ActiveCell.Value <> "" ActiveCell.Offset(1, 0).Select Loop

Bad way of Rnd() function usage

Rnd function

^

VB

randomValue = Int((upperbound - lowerbound + 1) * Rnd()) + lowerbound

Finally, i would do "lottery system" in that way:

VB

Function GetFirstEmptyRow(wsh As Worksheet, Optional sColName as String= "A") As Long
GetFirstEmptyRow = wsh.Range(sColName & wsh.Rows.Count).End(xlUp).Row +1
End Function

Sub RandomWinner()
'declare variables
Dim  firstrow As Long, lastrow As Long, winner As Long
Dim dstwsh As Worksheet

'set object varable, type of Worksheet
Set dstwsh = Thisworkbook.Worksheets("Sheet1")
'set firstrow and lastrow
firstrow = 2
lastrow = GetFirstEmptyRow(dstwsh) -1
'randomize
winner = Int((lastrow - firstrow + 1) * Rnd() + firstrow)
'select "winner"
dstwsh.Range("A" & winner).Select
Set dstwsh = Nothing
End Sub

Note: it would be good to add error handler[^] ;)

Posted 16-Dec-13 21:10pm

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Homero Rivera · Accepted Answer · 2013-12-16T16:36:00

LOL I just love Excel Macros and vbscript!!
Here, try this

VB

Sub SelectRandomName()

  'this asumes from cell A1 you have a name

  Dim LastRow
  Range("A1:A1").Select

  Do While ActiveCell.Value <> ""
    ActiveCell.Offset(1, 0).Select
  Loop

  LastRow = ActiveCell.Row - 1

  'You now have the number of names which is equal to the number of successive occupied cells
  'That while loop above goes on untill it finds an empty cell.

  'Now you only need to multiply by a random 0 to 1 coeficient the number of rows
  'and round that number to zero decimals.

  Dim TargetRow
  TargetRow = Rnd * LastRow

  TargetRow = Round(TargetRow)

  'Rnd is a really mean real number, it may aproach 0 really bad that it will put it below row 1
  'thus generating an error, or it may go out of range.
  'In case that happens we'll use an If condition to adjust.

  If TargetRow < 1 Then
    TargetRow = 1
  ElseIf TargetRow > LastRow Then
    TargetRow = LastRow
  End If


  Range("A" & CInt(TargetRow) & ":A" & CInt(TargetRow)).Select

End Sub

End Answer