HI,
Happy New Year to you all.i have avb code working ok,but I would like to add a “ do until “ statement so all columns “A to J” have only non- consecutive numbers ,is that possible ?
Any suggestion would be appreciated.
Here is the code;
an example of current result which i do not want;
Happy New Year to you all.i have avb code working ok,but I would like to add a “ do until “ statement so all columns “A to J” have only non- consecutive numbers ,is that possible ?
Any suggestion would be appreciated.
Here is the code;
Code:
Sub generateuniquerandom()Dim b() As Boolean, e As Range, k&, x&
ReDim b(1 To 60)
For Each e In Range("a1:j6")
Do
x = Int(Rnd() * 60) + 1
If b(x) = False Then
e.Value = x
b(x) = True
Exit Do
End If
k = k + 1: If k > 1000 Then Exit Sub
Loop
Next
Dim RgToSort As Range
Dim Rgcol As Range
Dim U As Long
Set RgToSort = Range(Range("A1:J6"), Range("A1:bez6").End(xlDown))
For Each Rgcol In RgToSort.Columns
Rgcol.Sort Key1:=Range(Rgcol.Item(1).Address), Order1:=xlAscending, Orientation:=xlTopToBottom, OrderCustom:=1
Next
End Sub
Code:
[TABLE="width: 460"]
<colgroup><col span="10"></colgroup><tbody>[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]52[/TD]
[/TR]
</tbody>[/TABLE]
Last edited: