Forum Discussion

Sumit_Bhokare's avatar
Sumit_Bhokare
Brass Contributor
Dec 23, 2021
Solved

How to put random number from list

Hello Team,   I have file in which there are few names in column A & Numbers are available in column C. What required is - can we have random single number from list C2:C18 at active cell after cl...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Dec 23, 2021

    Sumit_Bhokare 

    Code for the command button:

    Private Sub CommandButton1_Click()
    Static arr()
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim tmp As Long
    If ActiveWindow.RangeSelection.CountLarge > 1 Then Exit Sub
    If Intersect(Range("B2:B18"), ActiveCell) Is Nothing Then Exit Sub
    If Application.Count(Range("B2:B18")) = 0 Then
        arr = Range("C2:C18").Value
        For k = 1 To 5
            For i = 1 To 17
                j = Application.RandBetween(1, 17)
                If j <> i Then
                    tmp = arr(i, 1)
                    arr(i, 1) = arr(j, 1)
                    arr(j, 1) = tmp
                End If
            Next i
        Next k
    End If
    ActiveCell.Value = arr(ActiveCell.Row - 1, 1)
End Sub

Resources