Random N Numbers in an Array

[ionelz]

Hi,
I have this problem :
01. a variable rows N from 1 to 50, in sample N=9 (A1=9)
02. a variable columns C from 1 to 5, in sample C=5 (A2=5)
03. a Requirement R=2 (A3=2)

I did a manually but is difficult when numbers increase
Calculation :
Total of "X"=N*R=18
Limits L=N*R/C=9*2/5=3.6
Since L is not integer, Limits are 3 and 4 ,Integer(L) and integer(L)+1

I need this : every row to have R=2 of "X" and every column 3 and 4

 

[ionelz]

last one FREEZE for me

 

[Legacy 143009]

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

Ok, I am sharing with you the latest file. And the code should look like this:

Sub test()
  Dim counter As Long, lRow As Long, lCol As Long, maxCol As Long, L As Long, valR As Long, valL As Double, numRow As Long, numCol As Long
  Dim rng As Range, tmp As Long
  lRow = Range("I3").Value + 3
  lCol = Range("I4").Value + 1
  valR = Range("C1").Value
  valL = Range("C2").Value
  Set rng = Range(Range("B4"), Cells(lRow, lCol))
  Range("B4:F100").ClearContents
  counter = 0
  Application.ScreenUpdating = False
  With WorksheetFunction
  numRow = lRow - 3
  numCol = lCol - 1
  Do While tmp < numRow * valR
    For c = 1 To numCol
    L = IIf(maxCol = rng.Columns.Count, .RoundUp(valL, 0), .RoundDown(valL, 0))
      For r = 1 To numRow
        If r > .RandBetween(numRow * -1, numRow * 2) And .CountA(rng.Rows(r)) < valR And .CountA(rng.Columns(c)) < L Then
          If .RandBetween(0, 1) Then
            rng(r, c).Value = "X"
          End If
        End If
      Next
    Next
    maxCol = 0
    For i = 1 To numCol
      If .CountA(rng.Columns(i)) = .RoundDown(valL, 0) Then
        maxCol = maxCol + 1
      End If
    Next
    If counter Mod 3 = 0 Then
      If .CountA(rng) = tmp Then
        rng.ClearContents
      End If
    End If
    tmp = .CountA(rng)
    counter = counter + 1
  Loop
  End With
  Application.ScreenUpdating = True
End Sub

Now everything should be sorted.

 

[ionelz]

Works perfect now, thank you very much !

 

[Legacy 143009]

I am glad finally everything is fine. Are you happy with the distribution of values?

 

[ionelz]

Yes, every think work perfect now !

 

[Legacy 143009]

Ok one last optimization.
Change this line like this. It adjusts the number of passes according to R value.

If counter Mod (valR + 1) = 0 Then

For example when R is 4, old code waits very long. It is a bit shorter like this.

 

[ionelz]

Thank you