如何随机选择多个单元格并在消息框中显示内容? [英] How can I randomly select a number of cells and display the contents in a message box?

问题描述

我在单元格A1-A37中有ID号1101-1137的列表.我想单击一个按钮以随机选择20个(无重复)，并将它们显示在消息框中.

我现在所拥有的似乎是从数字1-37中随机选择的，而不是单元格的实际内容，而且我不知道如何解决它.例如，如果我从单元格A37中删除数字1137，则数字37仍然可以出现在消息框中.如果我将A5单元格中的数字1105替换为字母E，则E不会显示在消息框中，但5可以显示.

但是，如果我将"Const nItemsTotal As Long = 37"更改为等于其他数字，例如31，则只会输出1-31的数字.

这就是我所拥有的:

  Private Sub CommandButton1_Click()const nItemsToPick只要长= 20Const nItemsTotal as Long = 37昏暗列表作为范围昏暗的idx()只要长将varRandomItems()变暗昏暗的我只要Dim j As LongDim booIndexIsUnique为布尔值设置rngList = Range("A1").Resize(nItemsTotal，1)ReDim idx(1至nItemsToPick)ReDim varRandomItems(1至nItemsToPick)对于i = 1到nItemsToPick做booIndexIsUnique = True'被证明有罪之前无罪idx(i)= Int(nItemsTotal * Rnd + 1)对于j = 1到i-1如果idx(i)= idx(j)'已经在那里.booIndexIsUnique =假退出万一下一个j如果booIndexIsUnique = True，则strString = strString&vbCrLf&idx(i)退出做万一环形varRandomItems(i)= rngList.Cells(idx(i)，1)接下来我消息= strString消息框消息'varRandomItems现在包含nItemsToPick唯一随机'rngList范围内的项目.结束子 

我确定这是一个愚蠢的错误，但我迷路了.非常感谢您的帮助.

解决方案

我在您的代码中向一行添加了一点...该行现在为:

  strString = strString&vbCrLf&单元格(idx(i)，1).值 

完整代码是:

  Private Sub CommandButton1_Click()const nItemsToPick只要长= 20Const nItemsTotal as Long = 37昏暗列表作为范围昏暗的idx()一样长将varRandomItems()变暗昏暗的我只要Dim j As LongDim booIndexIsUnique为布尔值设置rngList = Range("A1").Resize(nItemsTotal，1)ReDim idx(1至nItemsToPick)ReDim varRandomItems(1至nItemsToPick)对于i = 1到nItemsToPick做booIndexIsUnique = True'被证明有罪之前无辜idx(i)= Int(nItemsTotal * Rnd + 1)对于j = 1到i-1如果idx(i)= idx(j)'已经在那里.booIndexIsUnique =假退出万一下一个j如果booIndexIsUnique = True，则strString = strString&vbCrLf&单元格(idx(i)，1).值退出做万一环形varRandomItems(i)= rngList.Cells(idx(i)，1)接下来我消息= strString消息框消息'varRandomItems现在包含nItemsToPick唯一随机'rngList范围内的项目.结束子 

因此，它不返回数字，而是使用返回的数字查看与之相关的行上的值.

I have a list of ID numbers 1101-1137 in cells A1-A37. I would like to click a button to randomly select 20 of these, with no repetitions, and display them in a message box.

What I have right now seems to randomly select from the numbers 1-37, not the actual contents of the cells, and I can't figure out how to fix it. For example, if I delete the number 1137 from cell A37, the number 37 can still end up in the message box; if I replace the number 1105 in cell A5 with the letter E, E will not show up in the message box but 5 can.

However, if I change "Const nItemsTotal As Long = 37" to equal some other number, say 31, it will only output numbers from 1-31.

This is what I have:

Private Sub CommandButton1_Click()

Const nItemsToPick As Long = 20
Const nItemsTotal As Long = 37

Dim rngList As Range
Dim idx() As Long
Dim varRandomItems() As Variant
Dim i As Long
Dim j As Long
Dim booIndexIsUnique As Boolean

Set rngList = Range("A1").Resize(nItemsTotal, 1)

ReDim idx(1 To nItemsToPick)
ReDim varRandomItems(1 To nItemsToPick)
For i = 1 To nItemsToPick
    Do
        booIndexIsUnique = True ' Innocent until proven guilty
        idx(i) = Int(nItemsTotal * Rnd + 1)
        For j = 1 To i - 1
            If idx(i) = idx(j) Then
                ' It's already there.
                booIndexIsUnique = False
                Exit For
            End If
        Next j
        If booIndexIsUnique = True Then
        strString = strString & vbCrLf & idx(i)
            Exit Do
        End If
    Loop
    varRandomItems(i) = rngList.Cells(idx(i), 1)

  Next i
    Msg = strString
    MsgBox Msg
' varRandomItems now contains nItemsToPick unique random
' items from range rngList.

End Sub

I'm sure it's a silly mistake, but I'm lost. Thank you so much for any help.

解决方案

I've added a little to one line in your code... the line is now:

strString = strString & vbCrLf & Cells(idx(i), 1).Value

the full code is:

Private Sub CommandButton1_Click()

Const nItemsToPick As Long = 20
Const nItemsTotal As Long = 37

Dim rngList As Range
Dim idx() As Long
Dim varRandomItems() As Variant
Dim i As Long
Dim j As Long
Dim booIndexIsUnique As Boolean

Set rngList = Range("A1").Resize(nItemsTotal, 1)

ReDim idx(1 To nItemsToPick)
ReDim varRandomItems(1 To nItemsToPick)
For i = 1 To nItemsToPick
    Do
        booIndexIsUnique = True ' Innocent until proven guilty
        idx(i) = Int(nItemsTotal * Rnd + 1)
        For j = 1 To i - 1
            If idx(i) = idx(j) Then
                ' It's already there.
                booIndexIsUnique = False
                Exit For
            End If
        Next j
        If booIndexIsUnique = True Then
        strString = strString & vbCrLf & Cells(idx(i), 1).Value
            Exit Do
        End If
    Loop
    varRandomItems(i) = rngList.Cells(idx(i), 1)

  Next i
    Msg = strString
    MsgBox Msg
' varRandomItems now contains nItemsToPick unique random
' items from range rngList.

End Sub

So rather than returning the number, it uses the number returned to look at the value on the row that it relates to.

这篇关于如何随机选择多个单元格并在消息框中显示内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！