how to get unique 30 random numbers from a list using VBA code

Question

i have list of numbers B201:B523 . i want to extract 30 random numbers from this list in E200:E230.

i used this code. It is giving only 1 random number in all output cell.

Sub Generate_random_values_from_a_column()

'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
ws.Range("e200:e229") = WorksheetFunction.Index(Range("B201:B523"), WorksheetFunction.RandBetween(1, ws.Range("B201:B523").Rows.Count), 1)

End Sub

then i tried some other codes but those were worse then this.

Answer 1

We can put the formula first in the cell and then change it to values.

Sub Generate_random_values_from_a_column()
    'declare a variable
    Dim ws As Worksheet
    Set ws = Worksheets("sheet1")
    
    With ws
        .Range("e200:e229").Formula = "=RANDBETWEEN(1, " & .Range("B201:B523").Rows.Count & ")"
        .Range("e200:e229").Value = .Range("e200:e229").Value
    End With
End Sub