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 click button.

 

i.e. if currently active cell is B2 & if we hit button then random number will be available at B2.

if active cell is B3 & if we hit button then random number will be available at B3

this is required till last name.

 

Doing this for birthday event within Team for surprise gift 🙂

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

6 Replies

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

      HansVogelaar actually want doing this live during discussion hence one by one is required.

      if its complicated then One go will also work, but one by one is preferred one 🙂

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        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