Reduire la formule

[Olyxier]

bonsoir le forum
comment peut on reduire cette formule merci à vous


Range("AP272😀V439").Select
Selection.ClearFormats
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 265
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 257

ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 115
ActiveWindow.Zoom = 75
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 271
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 265
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 262

ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 121
Range("B137").Select
ActiveCell.FormulaR1C1 = "=R[165]C[43]"
Range("B138").Select
ActiveCell.FormulaR1C1 = "=R[164]C[42]"
Range("B139").Select
ActiveCell.FormulaR1C1 = "=R[166]C[42]"
Range("B140").Select
ActiveCell.FormulaR1C1 = "=R[171]C[44]"
Range("B141").Select
ActiveCell.FormulaR1C1 = "=R[171]C[47]"
Range("B140:B141").Select
Selection.Cut
Range("C140").Select
ActiveSheet.Paste
Range("G137").Select
ActiveCell.FormulaR1C1 = "=R[174]C[42]"
Range("G138").Select
ActiveCell.FormulaR1C1 = "=R[177]C[42]"
Range("G139").Select
ActiveCell.FormulaR1C1 = "=R[180]C[42]"
Range("G140").Select
ActiveCell.FormulaR1C1 = "=R[183]C[42]"
Range("G141").Select
ActiveCell.FormulaR1C1 = "=R[186]C[42]"
Range("L139").Select

 

[allegro50]

Re : Reduire la formule

Bonsoir Olyxier, le Forum

C'est quoi le but de tous ces allers et retours parmi les colonnes ?
Ne peux tu aller directement en B137?
ca m'a l'air assez statique comme formules, pourquoi ne pas les taper ds les cellules qui vont bien plutot que de passer par VBA ?
Un petit exemple peut etre ?

A plus

 

[Pierrot93]

Re : Reduire la formule

Bonsoir Olivier, Allegro

Tu peux essayer le code ci dessous, ca devrait peut être marcher :

Range("AP272:AV439").ClearFormats

Range("B137").FormulaR1C1 = "=R[165]C[43]"
Range("B138").FormulaR1C1 = "=R[164]C[42]"
Range("B139").FormulaR1C1 = "=R[166]C[42]"
Range("B140").FormulaR1C1 = "=R[171]C[44]"
Range("B141").FormulaR1C1 = "=R[171]C[47]"
Range("B140:B141").Cut Range("C140")
Range("G137").FormulaR1C1 = "=R[174]C[42]"
Range("G138").FormulaR1C1 = "=R[177]C[42]"
Range("G139").FormulaR1C1 = "=R[180]C[42]"
Range("G140").FormulaR1C1 = "=R[183]C[42]"
Range("G141").FormulaR1C1 = "=R[186]C[42]"

Bonne soirée
@+

 

[Olyxier]

Re : Reduire la formule

MERCI bien joué