创建可生成唯一随机数的Excel VBA宏 [英] Creating excel VBA macro that generates unique random numbers

问题描述

我想创建一个本质上将根据用户输入返回随机数的宏，但是，我希望每个输出都是唯一的(这就是为什么 randbetween()函数将不起作用的原因)为了这).以下是到目前为止的内容，但仍然出现参考错误.我将这些代码与我在网上找到的一些不同示例结合在一起，因此以任何方式进行优化也将不胜感激.

I want to create a macro that will essentially return random numbers based on a users input, however, I want each output to be unique (which is why the randbetween() function won't work for this). Below is what I have so far, but I keep getting a reference error. I have stitched this code together from a few different examples I found online so optimizing in any way would also be appreciated.

代码:

Sub RandomSample()
Dim cell As Range
Dim rng As Range
Low = 1
High = Application.InputBox("Enter population total", Type:=1)
Sample = Application.InputBox("Enter the Sample Size", Type:=8)
Set rng = Application.Range(ActiveCell, ActiveCell.Offset(Sample, 0))
For Each cell In rng.Cells
    If WorksheetFunction.CountA(Selection) = (High - Low + 1) Then Exit For
    Do
        rndNumber = Int((High - Low + 1) * Rnd() + Low)
    Loop Until Selection.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
    cell.Value = rndNumber
Next
End Sub

错误窗口:错误图片

推荐答案

尝试一下

Sub RandomSample()
    Dim cell As Range
    Dim Sample As Range  'declare Sample as Range
    Low = 1
    High = Application.InputBox("Enter population total", Type:=1)
    Set Sample = Application.InputBox("Enter the Sample Size", Type:=8)
    For Each cell In Sample   'use sample in loop
        If WorksheetFunction.CountA(Sample) = (High - Low + 1) Then Exit For
        Do
            rndnumber = Int((High - Low + 1) * Rnd() + Low)
        Loop Until Sample.Cells.Find(rndnumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
        cell.Value = rndnumber
    Next
End Sub

EDIT :

Sub RandomSample()
    Dim cell As Range
    Dim rng As Range
    Dim High As Long, Sample As Long
    Low = 1
    High = Application.InputBox("Enter population total", Type:=1)
    Sample = Application.InputBox("Enter the Sample Size", Type:=1)
    Set rng = Application.Range(ActiveCell, ActiveCell.Offset(Sample, 0))
    For Each cell In rng.Cells
        If WorksheetFunction.CountA(rng) = (High - Low + 1) Then Exit For
        Do
            rndNumber = Int((High - Low + 1) * Rnd() + Low)
        Loop Until rng.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
        cell.Value = rndNumber
    Next
End Sub

这篇关于创建可生成唯一随机数的Excel VBA宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！