mercredi 29 avril 2020

I want to stop the loop each time it prints a value

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