Question

用excel，点击sheet2自动生成20个不重复的三位数，输出到A1:A20，需要编写VB代码，如何操作呢？

Answer 1

编好附上

Option Base 1

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Dim i, k As Long, mb(), n, rn

    If Sh.Name = "Sheet2" Then

        n1 = 100

        n2 = 999

        n = Int(n2) - Int(n1) + 1

        k = 0

        ReDim mb(n, 2)

        For i = Int(n1) To Int(n2)

            k = k + 1

            mb(k, 1) = k

            mb(k, 2) = i

        Next i

        Randomize

        On Error Resume Next

        ReDim mn(1 To n)

        With CreateObject("scripting.dictionary")

            For i = 1 To 20

                Randomize

                rn = Int(900 * Rnd + 100)

                .Add rn, Application.WorksheetFunction.VLookup(rn, mb, 2, False)

                If Err.Number <> 0 Then

                    i = i - 1

                End If

                Err.Clear

            Next i

            Range("a1").Resize(.Count, 1) = WorksheetFunction.Transpose(.items)

        End With

    End If

End Sub

代码放在

Excel列举两个整数间不重复随机数

Answer 2

Private Sub Worksheet_Activate()
Dim i, j As Integer
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
Dim arr(1 To 100, 1 To 1)
For i = 1 To 100
arr(i, 1) = Application.WorksheetFunction.RandBetween(100, 999)
dic(arr(i, 1)) = ""
Next

arr1 = dic.keys
For j = 1 To 20
Cells(j, 1) = arr1(j)
Next
Erase arr
Set dic = Nothing

End Sub

追问

谢谢你啊，sheet1中随机数有了，在切换工作表的时候，怎么自动生成这个随机数呢

追答

你把这个存放在sheet2，从其他工作表切换到sheet2的时候，自动生成随机数据。

Answer 3

Private Sub Worksheet_Activate()
Dim yrr(1 To 20, 1 To 1), i&, arr(1 To 900)
For i = 1 To 900
arr(i) = i + 99 + Application.RandBetween(0, 10000) * 1000
Next
For i = 1 To 20
yrr(i, 1) = Application.Small(arr, i) Mod 1000
Next
Range("a1:a20") = yrr
End Sub

追问

谢谢你啦，在切换工作表的时候，怎么让自动生成随机数呢

追答

双击蓝色标签，将代码粘贴到工作表事件里，就是你要的，一旦重新激活sheet2，就会自动生成随机数