Simplifier une macro

[mcj1997]

Bonjour,

Je pense que la macro ci-dessous doit pouvoir se simplifier mais je ne sais pas comment ?

Merci d'avance,

Sub MACRO()

For x = 2 To 11
Cells(5, x) = Cells(3, x).Value
Next
For x = 14 To 20
Cells(5, x) = Cells(3, x).Value
Next

For x = 2 To 11
Cells(10, x) = Cells(8, x).Value
Next
For x = 14 To 20
Cells(10, x) = Cells(8, x).Value
Next
For x = 2 To 11
Cells(15, x) = Cells(13, x).Value
Next
For x = 14 To 20
Cells(15, x) = Cells(13, x).Value
Next
For x = 2 To 11
Cells(20, x) = Cells(18, x).Value
Next
For x = 14 To 20
Cells(20, x) = Cells(18, x).Value
Next
For x = 2 To 9
Cells(27, x) = Cells(25, x).Value
Next
For x = 14 To 20
Cells(27, x) = Cells(25, x).Value
Next
For x = 2 To 9
Cells(32, x) = Cells(30, x).Value
Next
For x = 14 To 20
Cells(32, x) = Cells(30, x).Value
Next
For x = 2 To 9
Cells(37, x) = Cells(35, x).Value
Next
For x = 14 To 20
Cells(37, x) = Cells(35, x).Value
Next
For x = 2 To 9
Cells(42, x) = Cells(40, x).Value
Next
For x = 14 To 20
Cells(42, x) = Cells(40, x).Value
Next

For x = 2 To 11
Cells(49, x) = Cells(47, x).Value
Next
For x = 2 To 11
Cells(54, x) = Cells(52, x).Value
Next
For x = 2 To 11
Cells(59, x) = Cells(57, x).Value
Next
For x = 2 To 11
Cells(64, x) = Cells(62, x).Value
Next
For x = 2 To 11
Cells(71, x) = Cells(69, x).Value
Next
For x = 2 To 11
Cells(76, x) = Cells(74, x).Value
Next
For x = 2 To 11
Cells(81, x) = Cells(79, x).Value
Next
For x = 2 To 11
Cells(86, x) = Cells(84, x).Value
Next

For x = 2 To 7
Cells(93, x) = Cells(91, x).Value
Next
For x = 2 To 7
Cells(98, x) = Cells(96, x).Value
Next
For x = 2 To 7
Cells(103, x) = Cells(101, x).Value
Next
For x = 2 To 7
Cells(108, x) = Cells(106, x).Value
Next



End Sub

 

[Excel-lent]

Re : Simplifier une macro

Bonsoir Mcj1997,

Côté simplification au niveau de la lecture : tu peux faire des retraits de paragraphe.
Côté longueur du code, tu peux regrouper les boucles identiques entre elles.

C'est déjà plus facile à lire, et permet de voir si l'on peut trouver des redondances que l'on pourrait supprimer grâce à des boucles.

Au premier coup d'oeil, la partie en rouge et en bleu qui est deux fois : on pourrais faire des sous-macro
Le numéro de ligne de la cellule d'arrivé est toujours égale au numéro de ligne de la cellule d'arrivé - 2
Je ne sais pas simplifier ça, mais je l'ai vu faire sur le forum, c'est une histoire d'ofset (je ne sais plus l'orthographe exacte)

Sinon, concernant le ".value", il y a des cas où il ne sert à rien! Essaye de tester sans.

Sub MACRO()

For x = 2 To 11
[COLOR="Red"][B]  Cells(5, x) = Cells(3, x).Value
  Cells(10, x) = Cells(8, x).Value
  Cells(15, x) = Cells(13, x).Value
  Cells(20, x) = Cells(18, x).Value[/B][/COLOR]
  Cells(49, x) = Cells(47, x).Value
  Cells(54, x) = Cells(52, x).Value
  Cells(59, x) = Cells(57, x).Value
  Cells(64, x) = Cells(62, x).Value
  Cells(71, x) = Cells(69, x).Value
  Cells(76, x) = Cells(74, x).Value
  Cells(81, x) = Cells(79, x).Value
  Cells(86, x) = Cells(84, x).Value
Next

For x = 14 To 20
[COLOR="Red"][B]  Cells(5, x) = Cells(3, x).Value
  Cells(10, x) = Cells(8, x).Value
  Cells(15, x) = Cells(13, x).Value
  Cells(20, x) = Cells(18, x).Value[/B][/COLOR]
[COLOR="Blue"][B]  Cells(27, x) = Cells(25, x).Value
  Cells(32, x) = Cells(30, x).Value
  Cells(37, x) = Cells(35, x).Value
  Cells(42, x) = Cells(40, x).Value[/B][/COLOR]
Next

For x = 2 To 9
[COLOR="Blue"][B]  Cells(27, x) = Cells(25, x).Value
  Cells(32, x) = Cells(30, x).Value
  Cells(37, x) = Cells(35, x).Value
  Cells(42, x) = Cells(40, x).Value[/B][/COLOR]
Next

For x = 2 To 7
  Cells(93, x) = Cells(91, x).Value
  Cells(98, x) = Cells(96, x).Value
  Cells(103, x) = Cells(101, x).Value
  Cells(108, x) = Cells(106, x).Value
Next

End Sub

Mais je suis sûr que les spécialistes du code te le raccourciront à un point que ni toi ni moi ne pouvons imaginer!

 

[kjin]

Re : Simplifier une macro

Bonsoir,
A priori difficile de simplifier

Sub MACRO()

For x = 2 To 11
    For y = 5 To 20 Step 5
        Cells(y, x) = Cells(y - 2, x).Value
    Next
    For y = 49 To 86 Step 5
        Cells(y, x) = Cells(y - 2, x).Value
    Next
Next

For x = 14 To 20
    For y = 5 To 20 Step 5
        Cells(y, x) = Cells(y - 2, x).Value
    Next
    For y = 27 To 42 Step 5
        Cells(y, x) = Cells(y - 2, x).Value
    Next
Next

For x = 2 To 9
    For y = 27 To 42 Step 5
        Cells(y, x) = Cells(y - 2, x).Value
    Next
Next

For x = 2 To 7
    For y = 93 To 108 Step 5
        Cells(y, x) = Cells(y - 2, x).Value
    Next
Next

End Sub

A+
kjin