I use this code to get distinct random value in colum A of worksheet but this code gives me all the values at a time. i want to get the value one by one each time after clicking a worksheet button / active x button.
for example on first click the cell A1 should be filled with first random value and then for each click A2, A3, A4 AND A5 Cells shoudl be filled. Once all the celles are filled, on click of the button nothing shoudl happen.
Option Explicit
Sub RAND1()
Dim myStart As Long
Dim myEnd As Long
Dim i As Long
Dim a()
Dim sh As Worksheet
Set sh = Sheet1
myStart = 1
myEnd = 5
ReDim a(0 To myEnd - myStart)
With CreateObject("System.Collections.SortedList")
Randomize
For i = myStart To myEnd
.Item(Rnd) = i
Next i
For i = 0 To .Count - 1
a(i) = .GetByIndex(i)
Next
End With
sh.Range("a1").Resize(UBound(a) + 1).Value = Application.Transpose(a)
End Sub
Aucun commentaire:
Enregistrer un commentaire