excel宏从A1-A中随机选择一个单元格？

问题描述

我想从名单中选择一个单元格。它们占据了列A但行数未知。我想要一个宏，分配给一个按钮，我点击该按钮随机获得一个赢家。



提前感谢

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

推荐答案

1. 代码没有上下文


以上代码选择当前活动工作表中的单元格A1

请阅读此问题： []和我的回答


2. 使用选择是非常糟糕的编程实践除非是预期的效果。
3. 不必要的循环减慢代码的执行

Above code selects a cell A1 in currently active sheet!
Please, read this question: Dynamically Set Range in VBA for FillDown[^] and my answer

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


我建议使用单独的函数，就像我在推荐的帖子中所描述的那样。


6. 的坏方式Rnd（）功能使用
请看这里： []

它会是这样的：




I would suggest to use separate function as is i described in a referred thread.


7. Bad way of Rnd() function usage
Please, see here: Rnd function[^]
It would be something like that:


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












最后，我会以这种方式做彩票系统：





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


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





注意：最好添加 [];）




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




这篇关于excel宏从A1-A中随机选择一个单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！