Randomly Distribution of items across a range with vba

[kelly mort]

I have this table of the range

B2:J6

And I am looking for a way to randomly distribute the letters A-I across.

These are the rules:
1. A row can take just A or A and A . If 2 As, then they should be immediately adjacent each other. Example B2C2.

2. The first four letters can appear more than the last five.

3. Columns E and H are excluded.

I hope this is achievable.

Thanks in advance

 

[DanteAmor]

Your requirement clearly says column B.
Did you validate column B?
The macro works?

 

[kelly mort]

Yes it worked for column B greatly.

I even tried to test the order columns and very fine but I can't seem to know the right loop to use to capture all at a go.

 

[DanteAmor]

Just to be clear.
The macro works only for column B, because that's what you asked for. Obviously the macro does not work for the other columns.

 

[kelly mort]

Yes but I change

C.column = 2 , to 3, 4 etc and change the "B" as well to test.

So now I want to verify all the columns at a go.

 

[DanteAmor]

Ok. You modified the macro. It works for you?

 

[kelly mort]

Ok. You modified the macro. It works for you?

I couldn't get the loop to work for me.

But if I want to verify for say column C and I change that

If c.column = 2 Then
To 3, and update the range as well it verifies column C.


I want to verify all the columns at a go.

And I want you to fix that for me.

 

[DanteAmor]

I couldn't get the loop to work for me.

But if I want to verify for say column C and I change that

If c.column = 2 Then
To 3, and update the range as well it verifies column C.


I want to verify all the columns at a go.

And I want you to fix that for me.

Your example looks very nice, but in reality, with the random numbers and your rules we can find a flaw.

For example:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:24.71px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:24.71px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:24.71px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">1</td><td style="background-color:#d7e4bc; text-align:center; ">8</td><td style="background-color:#d7e4bc; text-align:center; ">0</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">6</td><td style="background-color:#d7e4bc; text-align:center; ">7</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">2</td><td style="background-color:#d7e4bc; text-align:center; ">2</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">0</td><td style="background-color:#d7e4bc; text-align:center; ">2</td><td style="background-color:#d7e4bc; text-align:center; ">7</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">4</td><td style="background-color:#d7e4bc; text-align:center; ">6</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">1</td><td style="background-color:#d7e4bc; text-align:center; ">3</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">7</td><td style="background-color:#d7e4bc; text-align:center; ">7</td><td style="background-color:#d7e4bc; text-align:center; ">5</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">0</td><td style="background-color:#d7e4bc; text-align:center; ">1</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">0</td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">2</td><td style="background-color:#ccc0da; text-align:center; ">7</td><td style="background-color:#ccc0da; text-align:center; ">6</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">4</td><td style="background-color:#ccc0da; text-align:center; ">0</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; "> </td><td style="background-color:#ccc0da; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">7</td><td style="background-color:#ccc0da; text-align:center; ">1</td><td style="background-color:#ccc0da; text-align:center; ">5</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">2</td><td style="background-color:#ccc0da; text-align:center; ">4</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; "> </td><td style="background-color:#ccc0da; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">0</td><td style="background-color:#ccc0da; text-align:center; ">5</td><td style="background-color:#ccc0da; text-align:center; ">7</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">6</td><td style="background-color:#ccc0da; text-align:center; ">3</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; "> </td><td style="background-color:#ccc0da; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">1</td><td style="background-color:#d7e4bc; text-align:center; ">5</td><td style="background-color:#d7e4bc; text-align:center; ">7</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">8</td><td style="background-color:#d7e4bc; text-align:center; ">3</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">2</td><td style="background-color:#d7e4bc; text-align:center; ">3</td><td style="background-color:#d7e4bc; text-align:center; ">0</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">6</td><td style="background-color:#d7e4bc; text-align:center; ">8</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">7</td><td style="background-color:#d7e4bc; text-align:center; ">1</td><td style="background-color:#d7e4bc; text-align:center; ">6</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">5</td><td style="background-color:#d7e4bc; text-align:center; ">2</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">5</td><td style="background-color:#ccc0da; text-align:center; ">1</td><td style="background-color:#ccc0da; text-align:center; ">8</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">3</td><td style="background-color:#ccc0da; text-align:center; ">6</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; "> </td><td style="background-color:#ccc0da; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">4</td><td style="background-color:#ccc0da; text-align:center; ">5</td><td style="background-color:#ccc0da; text-align:center; ">1</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">8</td><td style="background-color:#ccc0da; text-align:center; ">0</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; "> </td><td style="background-color:#ccc0da; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">7</td><td style="background-color:#ccc0da; text-align:center; ">6</td><td style="background-color:#ccc0da; text-align:center; ">0</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">5</td><td style="background-color:#ccc0da; text-align:center; ">2</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; "> </td><td style="background-color:#ccc0da; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">3</td><td style="background-color:#d7e4bc; text-align:center; ">2</td><td style="background-color:#d7e4bc; text-align:center; ">6</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">5</td><td style="background-color:#d7e4bc; text-align:center; ">8</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">5</td><td style="background-color:#d7e4bc; text-align:center; ">6</td><td style="background-color:#d7e4bc; text-align:center; ">1</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">8</td><td style="background-color:#d7e4bc; text-align:center; ">0</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">0</td><td style="background-color:#d7e4bc; text-align:center; ">8</td><td style="background-color:#d7e4bc; text-align:center; ">4</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">1</td><td style="background-color:#d7e4bc; text-align:center; ">3</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr></table>


In cell J4 the only allowed values ​​according to the row are 0,1,2,3 because the rule says that the first 4 numbers must be repeated more than the last numbers:

Okay I am going for the row.
So let's do the row.
For A, B, C and D.
That's first four items in the array.

In that same row 4 the numbers 7,7,5 were written, so in the next 4 positions of the row I can only put 0,1,2,3 (in your example A, B, C, D).

I can not put 0, because 2 zeros already exist in row 4.
I can not put 1, because there is already a 1 in row 4.
I can not put 2, because there is already a 2 in the same column.
I can not put 3, because there is already a 3 in the same column.


Your rules have an ambiguity.
The macro does a review of 50 attempts, if it can not put the number then it stops.

Option Explicit
Sub Randomly()
    Dim r As Range, c As Range
    Dim ale As Long, cnt As Long, n As Long, counter As Long, countB As Long
    Dim things As Variant, letter As String, exists As Boolean
    Dim rws As Variant, j As Long, rep As Long
    
    Application.ScreenUpdating = False
    
    things = Array("Apple", "Orange", "Mango", "Lime", "Lemon", "Banana", "Melon", "Pine", "Pear")
    
    rws = Array(2, 5, 8, 11, 14)
    Set r = Range("B2:D16,F2:G16,I2:J16")
    r.ClearContents
    For Each c In r
        exists = True
        n = UBound(things)
        Do While exists
            ale = WorksheetFunction.RandBetween(0, n)
            exists = False
            cnt = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), ale)
            If (cnt = 1 And c.Offset(, -1).Value <> ale) Or cnt = 2 Then exists = True
            
            counter = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), ">" & 3)
            If counter > 2 And ale > 3 Then
                exists = True
                n = 3
            End If
            For j = 0 To UBound(rws)
                If c.Row >= rws(j) And c.Row <= rws(j) + 2 Then
                    countB = WorksheetFunction.CountIf(Range(Cells(rws(j), c.Column), Cells(rws(j) + 2, c.Column)), ale)
                    If countB > 0 Then
                        exists = True
                    End If
                    Exit For
                End If
            Next
            rep = rep + 1
            If rep = 50 Then
                MsgBox "The bucle encountered a failure", vbCritical
                Exit Sub
            End If
        Loop
        c.Value = ale
        rep = 0
    Next
    For n = 0 To UBound(things)
        r.Replace n, things(n)
    Next
End Sub

 

[kelly mort]

Okay.

So without the rule of placing first four items more, can that flaw be eliminated? .


If yes then let us avoid that rule.

 

[DanteAmor]

Did you perform tests of the new version?

 

[kelly mort]

Did you perform tests of the new version?

Yes I did. It's very powerful and mind blowing.