Dispatching the numbers

[Galaxy_2019]

Good morning,

Since I'm bad at VBA, I need your help.

In cell S8 there is a numerical value which represents the number of columns authorized (manually editable value)
In cell S9 there is a numerical value which represents the maximum number to distribute (manually editable value)
In cell S10 there is a numerical value which represents the number of lines authorized (manually editable value)
I want to randomly distribute (with a VBA button) the numbers from 01 up to the number in cell S9 in the range D9:M100 according to the following conditions:
1- Random distribution in the range D9:M100 from 01 to cell number S9 in empty cells only and not repeating the same number in the same row, and replace the repeated number with another number
2- The number of columns in which the distribution is authorized is equal to the number in cell S8
3- The number of lines in which distribution is authorized is equal to the number in cell S10
4- The number of columns entered in cell S8 does not exceed the number entered in S9, if otherwise an error message is displayed
4 - The distribution is carried out only in the empty cells of the range D9:M100
5 - Allow the number to be repeated only once in each column
6 - compensation of cells containing letters with cells containing numbers at the end of the column (according to the conditions mentioned previously, with the exception of the condition of the number of lines).

Attached the file

Thank you.

[Galaxy_2019]

Good morning,

Can someone help me please

THANKS

[JohnTopley]

Add an example of your expected outcome.

[Galaxy_2019]

Hi JohnTopley,

I have already attached an example file in my first post

Thank You

[bebo021999]

I have already attached an example file in my first post

Fill in the numbers in the blank cells (could be correct, could be incorrect) and specify why it is wrong or why it is correct.

[Galaxy_2019]

I found two macros, the first Macro (Dispatche_1) which dispatches the numbers without duplicates throughout the table, and macro (Dispatche_2) which dispatches the numbers with repetition once in each column but it does not respect the condition of not repeat numbers in lines, I would like to combine between the two macros.

Public Alea, Nmax, Ncol
Sub Dispatche_1()
Dim L%, C%, Erreur$: ClearTable: Erreur = ""
Application.ScreenUpdating = False
If [S10] > [S9] Then Erreur = "Le nombre de colonne ne peut être inférieur au nombre max autorisé."
If [S10] > 10 Then Erreur = Erreur & Chr(10) & "Le nombre de colonnes max ne peut excéder 9."
If Erreur <> "" Then MsgBox Erreur: Exit Sub
Ncol = [S10]
RemplitTabloAlea
For L = 9 To 100
TirageAuSort
For C = 4 To 3 + Ncol
If Cells(L, C) = "" Then Cells(L, C) = Alea(C - 3, 1)
Next C
Next L
End Sub
Private Sub ClearTable() ' Efface toute cellule qui contient un nombre
Dim T, L%, C%
T = [D9:M100]
For L = 1 To UBound(T)
For C = 1 To UBound(T, 2)
If IsNumeric(T(L, C)) Then T(L, C) = ""
Next C
Next L
[D9].Resize(UBound(T, 1), UBound(T, 2)) = T
End Sub
Private Sub RemplitTabloAlea() ' Dimensionne le tableau Alea et remplit la première colonne avec les nombres de 1 à S9
Dim i%: ReDim Alea(1 To [S9], 1 To 2)
For i = 1 To UBound(Alea): Alea(i, 1) = i: Next i
End Sub
Private Sub TirageAuSort() ' Construit une matrice nombre alea de 0 à Nmax sans doublon
Dim i%, j%, Buffer%: Nmax = [S9]: Randomize
For i = 1 To UBound(Alea): Alea(i, 2) = Rnd: Next i ' Remplit la 2eme colonne avec des nombres aléatoires
For i = 1 To UBound(Alea) ' Tri le tableau en fonction des nombres aléatoires
For j = i To UBound(Alea)
If Alea(i, 2) < Alea(j, 2) Then ' Si A<B alors on inverse les deux valeurs
Buffer = Alea(i, 1): Alea(i, 1) = Alea(j, 1): Alea(j, 1) = Buffer
Buffer = Alea(i, 2): Alea(i, 2) = Alea(j, 2): Alea(j, 2) = Buffer
End If
Next j
Next i
End Sub

Sub Dispatche_2()
'Disable AutoCalc, screen, events
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

'Declaration of variables
Dim X&, Y&, i&, R&, cnt&

'Clear numbers from range
For Each C In Range("d9:m100")
If C.Value = [s1] Or C.Value = [s2] Or C.Value = [s3] Then GoTo 1
C.ClearContents
1 Next

'Processing
For i = 4 To [S10] + 3
cnt = Application.CountA(Range(Cells(9, i), Cells(100, i)))
For R = 9 To [s8] + 8 + cnt
If Not (IsEmpty(Cells(R, i))) Then GoTo 2
Randomize
3 X = Int(([S9]) * Rnd + 1)
Cells(R, i) = X
Y = WorksheetFunction.CountIf(Range(Cells(9, i), Cells(R, i)), X)
If Y > 2 Then GoTo 3
2 Next R
Next i

'Enable AutoCalc, screen, events
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Attached the file


Thank you