VBA Ajouter sur une colonne un numéro d'odre et un nombre aléatoire

[MJ13]

Bonjour à tous

Je ne suis toujours pas un grand spécialiste des tableaux .

J'ai une feuille avec 4 colonnes. Je peux connaître le nombre de lignes avec ce code. Mais ensuite, je voudrais le plus rapidement possible ajouter en colonne 5 un numéro d'ordre en incrémentant de 1 et en colonne 6 un numéro aléatoire de 1 à N (derl).

derl = Cells(Rows.Count, 1).End(xlUp).Row

Merci d'avance .

 

[bbb38]

Re : VBA Ajouter sur une colonne un numéro d'odre et un nombre aléatoire

Bonsoir Michel, le forum,
Une suggestion avec le fichier ci-joint.
Cordialement,
Bernard

 

[Robert]

Re : VBA Ajouter sur une colonne un numéro d'odre et un nombre aléatoire

Bonsoir le fil, bonsoir le forum,

Essaie comme ça :

derl = Cells(Rows.Count, 1).End(xlUp).Row
Randomize
Cells(derl, 5).Value = Application.WorksheetFunction.Max(Columns(5)) + 1
Cells(derl, 6).Value = Int((derl * Rnd) + 1)

 

[job75]

Re : VBA Ajouter sur une colonne un numéro d'odre et un nombre aléatoire

Bonsoir Michel, Robert,

2 solutions, avec et sans doublons :

Sub AleaAvecDoublons()
Dim derl&
derl = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
[E:F].ClearContents
With Range("E1:E" & derl)
  .Cells(1) = 1
  .DataSeries
  .Offset(, 1).FormulaLocal = "=ALEA.ENTRE.BORNES(1;" & derl & ")"
  .Offset(, 1) = .Offset(, 1).Value
End With
End Sub

Sub AleaSansDoublons()
Application.ScreenUpdating = False
[E:F].ClearContents
With Range("E1:E" & Range("A" & Rows.Count).End(xlUp).Row)
  .Formula = "=RAND()"
  .Value = .Value
  .Offset(, 1).Formula = "=RANK(RC[-1]," & .Address(ReferenceStyle:=xlR1C1) & ")"
  .Offset(, 1) = .Offset(, 1).Value
  .Cells(1) = 1
  .DataSeries
End With
End Sub

Je ne pense pas que des tableaux VBA seraient beaucoup plus rapides, mais ce serait à étudier.

Edit : bonsoir aussi bbb38.

A+

 

[MJ13]

Re : VBA Ajouter sur une colonne un numéro d'odre et un nombre aléatoire

Bonjour Bernard, Robert, Gérard

Merci beaucoup pour vos super codes .

Du coup, j'ai un peu testé sur quelques miliiers de lignes, c'est très rapide. Mais sur un exemple de 78 000 lignes , le code de Bernard , l'emporte.

Du coup vos codes m'ont donné lidée de faire ce code qui fonctionne mais, je n'ai pas supprimé les doublons , donc, c'est plus rapide.

Code VBA:
Public TimerT1, TimerT2, TimerT3, TimerT4
Sub Temps_Macro_Debut()
TimerT1 = Timer
End Sub
Sub Temps_Macro_Fin()
Application.StatusBar = Format(Timer - TimerT1, "0.00") & " secondes"
End Sub

Sub OrdreCol5_AleaCol6_MJ_Avec_Doublons()
Temps_Macro_Debut
derl = Cells(Rows.Count, 1).End(xlUp).Row
Randomize
Cells(2, 5).Value = 1
Range(Cells(3, 5), Cells(derl, 5)).FormulaR1C1 = "=R[-1]C+1"
Range(Cells(2, 6), Cells(derl, 6)).FormulaLocal = "=ENT((" & derl & "*alea()) + 1)"
Columns("E:F").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Temps_Macro_Fin
End Sub




Sinon pour le code de Robert ,c'est le plus rapide,mais il n'inscrit que sur la dernière ligne qu'une valeur , bon c'est vrai qu'il a pris ma demande en considérant ce que j'avais demandé "ajouter en colonne 5 un numéro d'ordre en incrémentant de 1 et en colonne 6 un numéro aléatoire de 1 à N".

Le code de Job sans doublons est impressionnant, il fonctionne bien et est très rapide sur des petites séries de quelques milliers de lignes.

Plutôt qu'un long discours, voici l'image avec les temps mesurés de mon test sur 78 000 lignes.

 

[job75]

Re : VBA Ajouter sur une colonne un numéro d'odre et un nombre aléatoire

Bonsoir à tous,

Oui ma solution sans doublon ne valait pas grand-chose.

Michel peux-tu tester cette nouvelle macro qui effectue un tri :

Sub AleaSansDoublons()
Application.ScreenUpdating = False
[E:F].ClearContents
With Range("E1:E" & Range("A" & Rows.Count).End(xlUp).Row)
  .Formula = "=RAND()"
  .Value = .Value
  .Offset(, 1).Cells(1) = 1
  .Offset(, 1).DataSeries
  .Resize(, 2).Sort .Cells(1) 'tri
  .Cells(1) = 1
  .DataSeries
End With
End Sub

A+

 

[MJ13]

Re : VBA Ajouter sur une colonne un numéro d'odre et un nombre aléatoire

Re, Bonjour Gérard

Chapeau Gérard , tu remportes le titre de champion du monde d'XLD pour l'année 2014.

1,34 seconde pour 282 871 lignes . Là, je dis, chapeau l'artiste .

Encore merci à tous .

 

[job75]

Re : VBA Ajouter sur une colonne un numéro d'odre et un nombre aléatoire

Bonjour Michel, le fil,

OK et merci, maintenant s'il y a des titres en ligne 1 :

Sub AleaAvecDoublons()
Dim derl&
Application.ScreenUpdating = False
Range("E2:F" & Rows.Count).ClearContents
derl = Range("A" & Rows.Count).End(xlUp).Row
If derl = 1 Then Exit Sub
With Range("E2:E" & derl)
  .Cells(1) = 1
  .DataSeries
  .Offset(, 1).FormulaLocal = "=ALEA.ENTRE.BORNES(1;" & derl & ")"
  .Offset(, 1) = .Offset(, 1).Value
End With
End Sub

Sub AleaSansDoublons()
Application.ScreenUpdating = False
Range("E2:F" & Rows.Count).ClearContents
With Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
  If .Row = 1 Then Exit Sub
  .Formula = "=RAND()"
  .Value = .Value
  .Cells(1, 2) = 1
  .Offset(, 1).DataSeries
  .Resize(, 2).Sort .Cells(1) 'tri
  .Cells(1) = 1
  .DataSeries
End With
End Sub

Bien sûr ce n'est pas plus long.

Edit : j'utilise ici .Cells(1, 2), plus simple.

A+

 

[MJ13]

Re : VBA Ajouter sur une colonne un numéro d'odre et un nombre aléatoire

Re Job


Merci pour ce nouveau code .

Bon, j'avoue qu'il y avait une ligne de titre. J'ai pu me débrouiller tout seul .