Help to Add loop to a VB code,Please..

S

sezuh

Well-known Member
Joined
Nov 19, 2010
Messages
708
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;
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
an example of current result which i do not want;
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:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Hi,

maybe this code can help (from snb)
Code: 
Sub iRnd()
'eindeutige zufällig verteilte ganze Zahlen 1-n
[a1:a10] = "=rand()"
Fen = [index(rank(a1:a10,a1:a10),)]
[a1:a10] = [index(rank(a1:a10,a1:a10),)]
End Sub

The range needs to be adjusted.

regards
 
Upvote 0
Try this:-
I'm sure this code can be improved on But at the moment:-
Code produces non consecutive columns
NB:- This code could fail and will need re-running if the last two value are consecutive.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG02Jan14
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
ReDim b(1 To 60) [COLOR="Navy"]As[/COLOR] Boolean
Randomize
Range("A1:J6").ClearContents
[COLOR="Navy"]For[/COLOR] Ac = 1 To 10
 [COLOR="Navy"]For[/COLOR] n = 1 To 6
    x = Int(Rnd() * 60) + 1
      [COLOR="Navy"]If[/COLOR] b(x) = False And Not temp + 1 = x And Not temp - 1 = x [COLOR="Navy"]Then[/COLOR]
          b(x) = True
            Cells(n, Ac) = x
      Else: x = Int(Rnd() * 60) + 1
        [COLOR="Navy"]Do[/COLOR] Until b(x) = False And Not temp + 1 = x And Not temp - 1 = x
            x = Int(Rnd() * 60) + 1
            K = K + 1
            [COLOR="Navy"]If[/COLOR] K > 2000 [COLOR="Navy"]Then[/COLOR] GoTo Ft
        [COLOR="Navy"]Loop[/COLOR]
        b(x) = True
        Cells(n, Ac) = x
     [COLOR="Navy"]End[/COLOR] If
     temp = x
  [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Ft: MsgBox "Re-Run code for comlete results"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
This a slight improvement :-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG02Jan31
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
ReDim b(1 To 60) [COLOR="Navy"]As[/COLOR] Boolean
Randomize
Range("A1:J6").ClearContents
[COLOR="Navy"]For[/COLOR] Ac = 1 To 10
 [COLOR="Navy"]For[/COLOR] n = 1 To 6
    x = Int(Rnd() * 60) + 1
        [COLOR="Navy"]Do[/COLOR] Until b(x) = False And Not temp + 1 = x And Not temp - 1 = x
            x = Int(Rnd() * 60) + 1
            K = K + 1
            [COLOR="Navy"]If[/COLOR] K > 2000 [COLOR="Navy"]Then[/COLOR] GoTo Ft
        [COLOR="Navy"]Loop[/COLOR]
        b(x) = True
        Cells(n, Ac) = x
        temp = x
  [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Ft: MsgBox "Re-Run code for complete results"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Mick and Fennek,
thanks for your help but both of your code did not give me my expected result,because after i sort the columns this is the result of Mick's
code;
Code: 
[TABLE="width: 640"]
 <colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 64, align: right"]2[/TD]
  [TD="class: xl65, width: 64, align: right"]1[/TD]
  [TD="class: xl65, width: 64, align: right"]19[/TD]
  [TD="class: xl66, width: 64, align: right"][COLOR=#00ff00]23[/COLOR][/TD]
  [TD="class: xl65, width: 64, align: right"]11[/TD]
  [TD="class: xl65, width: 64, align: right"]4[/TD]
  [TD="class: xl65, width: 64, align: right"]20[/TD]
  [TD="class: xl65, width: 64, align: right"]5[/TD]
  [TD="class: xl67, width: 64, align: right"][COLOR=#ff0000]8[/COLOR][/TD]
  [TD="class: xl65, width: 64, align: right"]3[/TD]
 [/TR]
 [TR]
  [TD="class: xl67, align: right"][COLOR=#ff0000]6[/COLOR][/TD]
  [TD="class: xl65, align: right"]28[/TD]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl66, align: right"][COLOR=#00ff00]24[/COLOR][/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl66, align: right"][COLOR=#ff0000]14[/COLOR][/TD]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl67, align: right"][COLOR=#ff0000]9[/COLOR][/TD]
  [TD="class: xl65, align: right"]12[/TD]
 [/TR]
 [TR]
  [TD="class: xl67, align: right"][COLOR=#ff0000]7[/COLOR][/TD]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]42[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]33[/TD]
  [TD="class: xl66, align: right"][COLOR=#ff0000]15[/COLOR][/TD]
  [TD="class: xl65, align: right"]38[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl66, align: right"][COLOR=#00ff00]16[/COLOR][/TD]
  [TD="class: xl65, align: right"]18[/TD]
 [/TR]
 [TR]
  [TD="class: xl66, align: right"][COLOR=#00ff00]30[/COLOR][/TD]
  [TD="class: xl65, align: right"]43[/TD]
  [TD="class: xl65, align: right"]53[/TD]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]41[/TD]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]44[/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl66, align: right"][COLOR=#00ff00]17[/COLOR][/TD]
  [TD="class: xl65, align: right"]25[/TD]
 [/TR]
 [TR]
  [TD="class: xl66, align: right"][COLOR=#00ff00]31[/COLOR][/TD]
  [TD="class: xl65, align: right"]46[/TD]
  [TD="class: xl65, align: right"]55[/TD]
  [TD="class: xl65, align: right"]45[/TD]
  [TD="class: xl65, align: right"]48[/TD]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl66, align: right"][COLOR=#ff0000]59[/COLOR][/TD]
  [TD="class: xl65, align: right"]39[/TD]
  [TD="class: xl65, align: right"]52[/TD]
  [TD="class: xl65, align: right"]32[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]57[/TD]
  [TD="class: xl65, align: right"]54[/TD]
  [TD="class: xl65, align: right"]58[/TD]
  [TD="class: xl65, align: right"]49[/TD]
  [TD="class: xl65, align: right"]51[/TD]
  [TD="class: xl65, align: right"]47[/TD]
  [TD="class: xl66, align: right"][COLOR=#ff0000]60[/COLOR][/TD]
  [TD="class: xl65, align: right"]50[/TD]
  [TD="class: xl65, align: right"]56[/TD]
  [TD="class: xl65, align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]
if you notice on five of columns had consecutive numbers ,after sorting. i do not want any column to have consecutive numbers at all if possible.
thanks again for your time and help,very much appreciated.
 
Upvote 0
here is an example ,how my result would be like;
Code: 
[TABLE="width: 640"]
 <colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 64, align: right"]1[/TD]
  [TD="class: xl65, width: 64, align: right"]2[/TD]
  [TD="class: xl65, width: 64, align: right"]3[/TD]
  [TD="class: xl65, width: 64, align: right"]4[/TD]
  [TD="class: xl65, width: 64, align: right"]5[/TD]
  [TD="class: xl65, width: 64, align: right"]6[/TD]
  [TD="class: xl65, width: 64, align: right"]7[/TD]
  [TD="class: xl65, width: 64, align: right"]8[/TD]
  [TD="class: xl65, width: 64, align: right"]9[/TD]
  [TD="class: xl65, width: 64, align: right"]10[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]17[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]19[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]28[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]38[/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]40[/TD]
  [TD="class: xl65, align: right"]39[/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]32[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]48[/TD]
  [TD="class: xl65, align: right"]49[/TD]
  [TD="class: xl65, align: right"]50[/TD]
  [TD="class: xl65, align: right"]41[/TD]
  [TD="class: xl65, align: right"]42[/TD]
  [TD="class: xl65, align: right"]43[/TD]
  [TD="class: xl65, align: right"]44[/TD]
  [TD="class: xl65, align: right"]45[/TD]
  [TD="class: xl65, align: right"]47[/TD]
  [TD="class: xl65, align: right"]50[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]57[/TD]
  [TD="class: xl65, align: right"]58[/TD]
  [TD="class: xl65, align: right"]59[/TD]
  [TD="class: xl65, align: right"]60[/TD]
  [TD="class: xl65, align: right"]51[/TD]
  [TD="class: xl65, align: right"]52[/TD]
  [TD="class: xl65, align: right"]53[/TD]
  [TD="class: xl65, align: right"]54[/TD]
  [TD="class: xl65, align: right"]55[/TD]
  [TD="class: xl65, align: right"]56[/TD]
[/TR]
</tbody>[/TABLE]
all numbers in the columns are non-consecutive.
 
Upvote 0
Try this:-
This code now sorts each column.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG03Jan58
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
ReDim b(1 To 60) [COLOR="Navy"]As[/COLOR] Boolean
Randomize
Range("A1:J6").ClearContents
[COLOR="Navy"]For[/COLOR] Ac = 1 To 10
 [COLOR="Navy"]For[/COLOR] n = 1 To 6
    x = Int(Rnd() * 59) + 1
       x = IIf(Ac Mod 2 = 0, Application.Even(x), Application.Odd(x))
        [COLOR="Navy"]Do[/COLOR] Until b(x) = False
            x = Int(Rnd() * 59) + 1
            x = IIf(Ac Mod 2 = 0, Application.Even(x), Application.Odd(x))
        [COLOR="Navy"]Loop[/COLOR]
        b(x) = True
        Cells(n, Ac) = x
 [COLOR="Navy"]Next[/COLOR] n
 Cells(1, Ac).Resize(6).Sort Cells(1, Ac), xlAscending
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Mick,
thanks for your help ,your code does not give the result i want ,does not mix the odd and even numbers in columns.
it gives one columns with odd and the other with even numbers.here is one of the result of your code;
Code: 
[TABLE="width: 640"]
 <colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl63, width: 64, align: right"]5[/TD]
  [TD="class: xl63, width: 64, align: right"]24[/TD]
  [TD="class: xl63, width: 64, align: right"]21[/TD]
  [TD="class: xl63, width: 64, align: right"]6[/TD]
  [TD="class: xl63, width: 64, align: right"]9[/TD]
  [TD="class: xl63, width: 64, align: right"]2[/TD]
  [TD="class: xl63, width: 64, align: right"]3[/TD]
  [TD="class: xl63, width: 64, align: right"]8[/TD]
  [TD="class: xl63, width: 64, align: right"]1[/TD]
  [TD="class: xl63, width: 64, align: right"]4[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]7[/TD]
  [TD="class: xl63, align: right"]28[/TD]
  [TD="class: xl63, align: right"]33[/TD]
  [TD="class: xl63, align: right"]14[/TD]
  [TD="class: xl63, align: right"]11[/TD]
  [TD="class: xl63, align: right"]16[/TD]
  [TD="class: xl63, align: right"]15[/TD]
  [TD="class: xl63, align: right"]10[/TD]
  [TD="class: xl63, align: right"]17[/TD]
  [TD="class: xl63, align: right"]12[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]13[/TD]
  [TD="class: xl63, align: right"]42[/TD]
  [TD="class: xl63, align: right"]39[/TD]
  [TD="class: xl63, align: right"]26[/TD]
  [TD="class: xl63, align: right"]25[/TD]
  [TD="class: xl63, align: right"]20[/TD]
  [TD="class: xl63, align: right"]31[/TD]
  [TD="class: xl63, align: right"]18[/TD]
  [TD="class: xl63, align: right"]19[/TD]
  [TD="class: xl63, align: right"]30[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]43[/TD]
  [TD="class: xl63, align: right"]44[/TD]
  [TD="class: xl63, align: right"]45[/TD]
  [TD="class: xl63, align: right"]32[/TD]
  [TD="class: xl63, align: right"]41[/TD]
  [TD="class: xl63, align: right"]22[/TD]
  [TD="class: xl63, align: right"]35[/TD]
  [TD="class: xl63, align: right"]38[/TD]
  [TD="class: xl63, align: right"]23[/TD]
  [TD="class: xl63, align: right"]34[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]47[/TD]
  [TD="class: xl63, align: right"]46[/TD]
  [TD="class: xl63, align: right"]49[/TD]
  [TD="class: xl63, align: right"]48[/TD]
  [TD="class: xl63, align: right"]55[/TD]
  [TD="class: xl63, align: right"]50[/TD]
  [TD="class: xl63, align: right"]37[/TD]
  [TD="class: xl63, align: right"]40[/TD]
  [TD="class: xl63, align: right"]27[/TD]
  [TD="class: xl63, align: right"]36[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]51[/TD]
  [TD="class: xl63, align: right"]56[/TD]
  [TD="class: xl63, align: right"]53[/TD]
  [TD="class: xl63, align: right"]58[/TD]
  [TD="class: xl63, align: right"]59[/TD]
  [TD="class: xl63, align: right"]60[/TD]
  [TD="class: xl63, align: right"]57[/TD]
  [TD="class: xl63, align: right"]54[/TD]
  [TD="class: xl63, align: right"]29[/TD]
  [TD="class: xl63, align: right"]52[/TD]
[/TR]
</tbody>[/TABLE]
if there is no solution for this problem its ok. you done more than enough for me and i thank you for that,very much appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

rjplante
  • Question
sort numbers with letters
2
Replies
17
Views
189
Akuini
Akuini
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
305
MARK858
MARK858
D
  • Solved
Macro that won't run Trying to use DoWhile Loop and Offset to compare cells
Replies
8
Views
208
Micron
M
Z
  • Solved
VBA Code to Hide columns in Specific Intervals
2
Replies
11
Views
372
jolivanes
jolivanes
M
  • Solved
Changing the position of a message box
Replies
1
Views
288
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,214,593
Messages
6,120,435
Members
448,962
Latest member
Fenes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top