Tirer formule vers le bas

Le 17/03/2023 à 17:05

Bonjour à toutes et tous,

dans le code ci-dessous je souhaite recopier une formule vers le bas quelque soit le nombre de ligne. J'ai essayé pas mal de possibilités avec Xlup mais je sèche.

    ActiveCell.FormulaR1C1 = "Disponible sur phasage"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-7],2)=""AP"",RC[-3]-RC[-1]-RC[1],0)"
    Range("P2").Select
    Selection.AutoFill Destination:=Range("P2:P891")
    Range("P2:P891").Select
    Columns("S:S").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Disponible budgétaire intégrant AP"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-10],2)=""AP"",RC[-1]-RC[-3],RC[-1])"
    Range("S2").Select
    Selection.AutoFill Destination:=Range("S2:S891")
    Range("S2:S891").Select

Je vous remercie pour votre aide.

legreffier

Le 17/03/2023 à 19:26

Bonsoir

A essayer

With ActiveSheet
    .Range("P1").FormulaR1C1 = "Disponible sur phasage"
    .Range("P2").FormulaR1C1 = "=IF(LEFT(RC[-7],2)=""AP"",RC[-3]-RC[-1]-RC[1],0)"
    dlg = .Range("P" & Rows.Count).End(xlUp).Row
    .Range("P2").AutoFill Destination:=Range("P2:P" & dlg)
    .Columns("S:S").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("S1").FormulaR1C1 = "Disponible budgétaire intégrant AP"
    .Range("S2").FormulaR1C1 = "=IF(LEFT(RC[-10],2)=""AP"",RC[-1]-RC[-3],RC[-1])"
    .Range("S2").AutoFill Destination:=Range("S2:S" & dlg)
End With

Ce serait bien de préciser sur quelle feuille vous êtes à la place de Activesheet

Si ok -->

Cordialement

Le 18/03/2023 à 12:29

Bonjour Dan, j'ai un bug :

image 2023 03 18 122748988

Je te donne le bout de code avant et après, au cas où c'est important.

    Rows("1:1").Select
    Selection.RowHeight = 60
    Selection.ColumnWidth = 20
    ActiveWindow.SmallScroll ToRight:=11
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("P1").Select
    .Range("P1").FormulaR1C1 = "Disponible sur phasage"
    .Range("P2").FormulaR1C1 = "=IF(LEFT(RC[-7],2)=""AP"",RC[-3]-RC[-1]-RC[1],0)"
    dlg = .Range("P" & Rows.Count).End(xlUp).Row
    .Range("P2").AutoFill Destination:=Range("P2:P" & dlg)
    .Columns("S:S").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("S1").FormulaR1C1 = "Disponible budgétaire intégrant AP"
    .Range("S2").FormulaR1C1 = "=IF(LEFT(RC[-10],2)=""AP"",RC[-1]-RC[-3],RC[-1])"
    .Range("S2").AutoFill Destination:=Range("S2:S" & dlg)
    Columns("T:T").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("U:U").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

Merci.

legreffier

Le 19/03/2023 à 08:33

Bonjour Dan,

je t'ai mis le classeur complet avec des valeurs bidons pour l'exemple.

Merci.

legreffier

14exemple.zip (306.85 Ko)
Le 19/03/2023 à 11:17

Bonjour,

Evidemment si vous ne prenez qu'une partie du code que je vous ai donné, comment voulez-vous que cela fonctionne.

Le 19/03/2023 à 12:11

Dan, pouvez-vous m'aider pour me dire comment intégrer votre code.

Je pense que je n'ai pas encore les compétences pour cela.

Merci

legreffier

Le 19/03/2023 à 12:31

Déjà
- supprimez tout ce qui est avant --> Columns("P:P").Select
- supprimez tout ce qui est après --> Range("S2:S891").Select

Ensuite vous mettez le code ci-dessous (identique au précédent mais auquel j'ai modifié une ligne et rajouté la ligne pour insérer la colonne P

Sub Doudou2()
Dim dlg as integer

With ActiveSheet
    .Columns("P:P").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("P1").FormulaR1C1 = "Disponible sur phasage"
    .Range("P2").FormulaR1C1 = "=IF(LEFT(RC[-7],2)=""AP"",RC[-3]-RC[-1]-RC[1],0)"
    dlg = .UsedRange.Rows.Count
    .Range("P2").AutoFill Destination:=Range("P2:P" & dlg)
    .Columns("S:S").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("S1").FormulaR1C1 = "Disponible budgétaire intégrant AP"
    .Range("S2").FormulaR1C1 = "=IF(LEFT(RC[-10],2)=""AP"",RC[-1]-RC[-3],RC[-1])"
    .Range("S2").AutoFill Destination:=Range("S2:S" & dlg)
End With
End sub
Le 20/03/2023 à 18:15

Bonjour Dan,

je ne comprends pas vraiment.

Si je supprime ce que vous dites le reste de mon code ne sert plus à rien.

En fait je dois juste modifier le code existant pour tirer une formule vers le bas.

Je vous joins le code en entier que j'ai fait avec l'enregistreur de macro :

Sub Doudou2()
'
' Doudou2 Macro
'

'
    ActiveWindow.FreezePanes = True
    ActiveWindow.FreezePanes = False
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Cells.Select
    Selection.EntireRow.Hidden = False
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    Selection.EntireColumn.Hidden = False
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1:S1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Abadi"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 56
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 56
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 56
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Rows("1:1").Select
    Selection.RowHeight = 60
    Selection.ColumnWidth = 20
    ActiveWindow.SmallScroll ToRight:=11
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Disponible sur phasage"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-7],2)=""ap"",RC[-3]-RC[-1]-RC[1],0)"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-7],2)=""AP"",RC[-3]-RC[-1]-RC[1],0)"
    Range("P2").Select
    Selection.AutoFill Destination:=Range("P2:P891")
    Range("P2:P891").Select
    Columns("S:S").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Disponible budgétaire intégrant AP"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-10],2)=""AP"",RC[-1]-RC[-3],RC[-1])"
    Range("S2").Select
    Selection.AutoFill Destination:=Range("S2:S891")
    Range("S2:S891").Select
    Columns("T:T").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("U:U").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    'Cells.Select
    Columns("A:S").Select
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(11, 12, 13 _
        , 14, 15, 16, 17, 18, 19), Replace:=True, PageBreaks:=False, SummaryBelowData:= _
        True
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(11, 12, 13 _
        , 14, 15, 16, 17, 18, 19), Replace:=False, PageBreaks:=False, SummaryBelowData _
        :=True
    Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(11, 12, 13 _
        , 14, 15, 16, 17, 18, 19), Replace:=False, PageBreaks:=False, SummaryBelowData _
        :=True
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 182
    ActiveWindow.ScrollRow = 198
    ActiveWindow.ScrollRow = 213
    ActiveWindow.ScrollRow = 220
    ActiveWindow.ScrollRow = 232
    ActiveWindow.ScrollRow = 238
    ActiveWindow.ScrollRow = 245
    ActiveWindow.ScrollRow = 251
    ActiveWindow.ScrollRow = 257
    ActiveWindow.ScrollRow = 261
    ActiveWindow.ScrollRow = 267
    ActiveWindow.ScrollRow = 273
    ActiveWindow.ScrollRow = 285
    ActiveWindow.ScrollRow = 318
    ActiveWindow.ScrollRow = 334
    ActiveWindow.ScrollRow = 346
    ActiveWindow.ScrollRow = 358
    ActiveWindow.ScrollRow = 378
    ActiveWindow.ScrollRow = 425
    ActiveWindow.ScrollRow = 436
    ActiveWindow.ScrollRow = 440
    ActiveWindow.ScrollRow = 445
    ActiveWindow.ScrollRow = 451
    ActiveWindow.ScrollRow = 456
    ActiveWindow.ScrollRow = 460
    ActiveWindow.ScrollRow = 466
    ActiveWindow.ScrollRow = 470
    ActiveWindow.ScrollRow = 475
    ActiveWindow.ScrollRow = 476
    ActiveWindow.ScrollRow = 480
    ActiveWindow.ScrollRow = 482
    ActiveWindow.ScrollRow = 486
    ActiveWindow.ScrollRow = 488
    ActiveWindow.ScrollRow = 490
    ActiveWindow.ScrollRow = 494
    ActiveWindow.ScrollRow = 498
    ActiveWindow.ScrollRow = 502
    ActiveWindow.ScrollRow = 509
    ActiveWindow.ScrollRow = 514
    ActiveWindow.ScrollRow = 518
    ActiveWindow.ScrollRow = 523
    ActiveWindow.ScrollRow = 527
    ActiveWindow.ScrollRow = 533
    ActiveWindow.ScrollRow = 540
    ActiveWindow.ScrollRow = 542
    ActiveWindow.ScrollRow = 546
    ActiveWindow.ScrollRow = 548
    ActiveWindow.ScrollRow = 552
    ActiveWindow.ScrollRow = 556
    ActiveWindow.ScrollRow = 569
    ActiveWindow.ScrollRow = 585
    ActiveWindow.ScrollRow = 593
    ActiveWindow.ScrollRow = 599
    ActiveWindow.ScrollRow = 601
    ActiveWindow.ScrollRow = 604
    ActiveWindow.ScrollRow = 608
    ActiveWindow.ScrollRow = 611
    ActiveWindow.ScrollRow = 616
    ActiveWindow.ScrollRow = 619
    ActiveWindow.ScrollRow = 623
    ActiveWindow.ScrollRow = 627
    ActiveWindow.ScrollRow = 630
    ActiveWindow.ScrollRow = 631
    ActiveWindow.ScrollRow = 634
    ActiveWindow.ScrollRow = 635
    ActiveWindow.ScrollRow = 638
    ActiveWindow.ScrollRow = 640
    ActiveWindow.ScrollRow = 642
    ActiveWindow.ScrollRow = 652
    ActiveWindow.ScrollRow = 655
    ActiveWindow.ScrollRow = 657
    ActiveWindow.ScrollRow = 659
    ActiveWindow.ScrollRow = 665
    ActiveWindow.ScrollRow = 667
    ActiveWindow.ScrollRow = 670
    ActiveWindow.ScrollRow = 677
    ActiveWindow.ScrollRow = 678
    ActiveWindow.ScrollRow = 681
    ActiveWindow.ScrollRow = 683
    ActiveWindow.ScrollRow = 686
    ActiveWindow.ScrollRow = 687
    ActiveWindow.ScrollRow = 689
    ActiveWindow.ScrollRow = 690
    ActiveWindow.ScrollRow = 691
    ActiveWindow.ScrollRow = 693
    ActiveWindow.ScrollRow = 698
    ActiveWindow.ScrollRow = 700
    ActiveWindow.ScrollRow = 701
    ActiveWindow.ScrollRow = 702
    ActiveWindow.ScrollRow = 701
    ActiveWindow.ScrollRow = 697
    ActiveWindow.ScrollRow = 694
    ActiveWindow.ScrollRow = 678
    ActiveWindow.ScrollRow = 674
    ActiveWindow.ScrollRow = 673
    ActiveWindow.ScrollRow = 671
    ActiveWindow.ScrollRow = 670
    ActiveWindow.ScrollRow = 669
    ActiveWindow.ScrollRow = 667
    ActiveWindow.ScrollRow = 666
    ActiveWindow.ScrollRow = 665
    ActiveWindow.ScrollRow = 663
    ActiveWindow.ScrollRow = 662
    ActiveWindow.ScrollRow = 661
    ActiveWindow.ScrollRow = 659
    ActiveWindow.ScrollRow = 658
    ActiveWindow.ScrollRow = 657
    ActiveWindow.ScrollRow = 655
    ActiveWindow.ScrollRow = 654
    ActiveWindow.ScrollRow = 651
    ActiveWindow.ScrollRow = 650
    ActiveWindow.ScrollRow = 648
    ActiveWindow.ScrollRow = 647
    ActiveWindow.ScrollRow = 646
    ActiveWindow.ScrollRow = 644
    ActiveWindow.ScrollRow = 643
    ActiveWindow.ScrollRow = 642
    ActiveWindow.ScrollRow = 640
    ActiveWindow.ScrollRow = 639
    ActiveWindow.ScrollRow = 638
    ActiveWindow.ScrollRow = 636
    ActiveWindow.ScrollRow = 638
    ActiveWindow.ScrollRow = 652
    ActiveWindow.ScrollRow = 669
    ActiveWindow.ScrollRow = 670
    ActiveWindow.ScrollRow = 671
    ActiveWindow.ScrollRow = 673
    ActiveWindow.ScrollRow = 674
    ActiveWindow.ScrollRow = 677
    ActiveWindow.ScrollRow = 678
    ActiveWindow.ScrollRow = 681
    ActiveWindow.ScrollRow = 683
    ActiveWindow.ScrollRow = 685
    ActiveWindow.ScrollRow = 686
    ActiveWindow.ScrollRow = 689
    ActiveWindow.ScrollRow = 691
    ActiveWindow.ScrollRow = 694
    ActiveWindow.ScrollRow = 695
    ActiveWindow.ScrollRow = 698
    ActiveWindow.ScrollRow = 700
    ActiveWindow.ScrollRow = 704
    ActiveWindow.ScrollRow = 706
    ActiveWindow.ScrollRow = 708
    ActiveWindow.ScrollRow = 710
    ActiveWindow.ScrollRow = 712
    ActiveWindow.ScrollRow = 713
    ActiveWindow.ScrollRow = 714
    ActiveWindow.ScrollRow = 716
    ActiveWindow.ScrollRow = 717
    ActiveWindow.ScrollRow = 718
    ActiveWindow.ScrollRow = 721
    ActiveWindow.ScrollRow = 722
    ActiveWindow.ScrollRow = 725
    ActiveWindow.ScrollRow = 726
    ActiveWindow.ScrollRow = 728
    ActiveWindow.ScrollRow = 729
    ActiveWindow.ScrollRow = 730
    ActiveWindow.ScrollRow = 732
    ActiveWindow.ScrollRow = 733
    ActiveWindow.ScrollRow = 736
    ActiveWindow.ScrollRow = 737
    ActiveWindow.ScrollRow = 740
    ActiveWindow.ScrollRow = 743
    ActiveWindow.ScrollRow = 745
    ActiveWindow.ScrollRow = 748
    ActiveWindow.ScrollRow = 749
    ActiveWindow.ScrollRow = 751
    ActiveWindow.ScrollRow = 753
    ActiveWindow.ScrollRow = 756
    ActiveWindow.ScrollRow = 757
    ActiveWindow.ScrollRow = 759
    ActiveWindow.ScrollRow = 760
    ActiveWindow.ScrollRow = 761
    ActiveWindow.ScrollRow = 763
    ActiveWindow.ScrollRow = 764
    ActiveWindow.ScrollRow = 765
    ActiveWindow.ScrollRow = 767
    ActiveWindow.ScrollRow = 768
    ActiveWindow.ScrollRow = 769
    ActiveWindow.ScrollRow = 771
    ActiveWindow.ScrollRow = 772
    ActiveWindow.ScrollRow = 773
    ActiveWindow.ScrollRow = 776
    ActiveWindow.ScrollRow = 777
    ActiveWindow.ScrollRow = 779
    ActiveWindow.ScrollRow = 782
    ActiveWindow.ScrollRow = 784
    ActiveWindow.ScrollRow = 786
    ActiveWindow.ScrollRow = 787
    ActiveWindow.ScrollRow = 788
    ActiveWindow.ScrollRow = 790
    ActiveWindow.ScrollRow = 791
    ActiveWindow.ScrollRow = 792
    ActiveWindow.ScrollRow = 795
    ActiveWindow.ScrollRow = 796
    ActiveWindow.ScrollRow = 798
    ActiveWindow.ScrollRow = 799
    ActiveWindow.ScrollRow = 800
    ActiveWindow.ScrollRow = 802
    ActiveWindow.ScrollRow = 803
    ActiveWindow.ScrollRow = 804
    ActiveWindow.ScrollRow = 806
    ActiveWindow.ScrollRow = 807
    ActiveWindow.ScrollRow = 810
    ActiveWindow.ScrollRow = 811
    ActiveWindow.ScrollRow = 812
    ActiveWindow.ScrollRow = 814
    ActiveWindow.ScrollRow = 815
    ActiveWindow.ScrollRow = 816
    ActiveWindow.ScrollRow = 818
    ActiveWindow.ScrollRow = 820
    ActiveWindow.ScrollRow = 822
    ActiveWindow.ScrollRow = 825
    ActiveWindow.ScrollRow = 827
    ActiveWindow.ScrollRow = 830
    ActiveWindow.ScrollRow = 831
    ActiveWindow.ScrollRow = 833
    ActiveWindow.ScrollRow = 834
    ActiveWindow.ScrollRow = 835
    ActiveWindow.ScrollRow = 837
    ActiveWindow.ScrollRow = 838
    ActiveWindow.ScrollRow = 839
    ActiveWindow.ScrollRow = 841
    ActiveWindow.ScrollRow = 842
    ActiveWindow.ScrollRow = 843
    ActiveWindow.ScrollRow = 845
    ActiveWindow.ScrollRow = 846
    ActiveWindow.ScrollRow = 847
    ActiveWindow.ScrollRow = 849
    ActiveWindow.ScrollRow = 850
    ActiveWindow.ScrollRow = 851
    ActiveWindow.ScrollRow = 853
    ActiveWindow.ScrollRow = 854
    ActiveWindow.ScrollRow = 855
    ActiveWindow.ScrollRow = 857
    ActiveWindow.ScrollRow = 858
    ActiveWindow.ScrollRow = 859
    ActiveWindow.ScrollRow = 861
    ActiveWindow.ScrollRow = 862
    ActiveWindow.ScrollRow = 864
    ActiveWindow.ScrollRow = 865
    ActiveWindow.ScrollRow = 866
    ActiveWindow.ScrollRow = 868
    ActiveWindow.ScrollRow = 869
    ActiveWindow.ScrollRow = 870
    ActiveWindow.ScrollRow = 872
    ActiveWindow.ScrollRow = 873
    ActiveWindow.ScrollRow = 874
    ActiveWindow.ScrollRow = 876
    ActiveWindow.ScrollRow = 877
    ActiveWindow.ScrollRow = 878
    ActiveWindow.ScrollRow = 880
    ActiveWindow.ScrollRow = 881
    ActiveWindow.ScrollRow = 882
    ActiveWindow.ScrollRow = 878
    ActiveWindow.ScrollRow = 872
    ActiveWindow.ScrollRow = 849
    ActiveWindow.ScrollRow = 763
    ActiveWindow.ScrollRow = 744
    ActiveWindow.ScrollRow = 725
    ActiveWindow.ScrollRow = 694
    ActiveWindow.ScrollRow = 673
    ActiveWindow.ScrollRow = 650
    ActiveWindow.ScrollRow = 631
    ActiveWindow.ScrollRow = 608
    ActiveWindow.ScrollRow = 572
    ActiveWindow.ScrollRow = 505
    ActiveWindow.ScrollRow = 431
    ActiveWindow.ScrollRow = 342
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 206
    ActiveWindow.ScrollRow = 186
    ActiveWindow.ScrollRow = 158
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 144
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 136
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    Rows("1:1").Select
    Range("L1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.RowHeight = 30
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.ScrollColumn = 13
    Columns("K:U").Select
    Range("U1").Activate
    Columns("K:U").EntireColumn.AutoFit
    Selection.ColumnWidth = 14.22
    Selection.ColumnWidth = 12.33
    Columns("U:U").Select
    Selection.ColumnWidth = 6.67
    Columns("U:U").EntireColumn.AutoFit
    Columns("U:U").EntireColumn.AutoFit
    Columns("U:U").EntireColumn.AutoFit
    Selection.ColumnWidth = 7.11
    Columns("U:U").EntireColumn.AutoFit
    Selection.ColumnWidth = 10
    Columns("U:U").EntireColumn.AutoFit
    Range("T1").Select
    Columns("T:T").ColumnWidth = 12.44
    Columns("T:T").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=0
    Columns("S:S").Select
    Selection.ColumnWidth = 15
    Range("A1").Select
    ActiveSheet.Shapes.Range(Array("Smiley Face 4")).Select
    Selection.Delete
End Sub

Merci.

legreffier

Le 20/03/2023 à 18:21

Si je supprime ce que vous dites le reste de mon code ne sert plus à rien.

Le reste du code c'est de la mise en forme. Le plus simple est de faire cela dans un code séparé et de l'exécuter après ce que vous demandez dans ce fil à savoir "Tirer une formule vers le bas"

Donc faites d'abord ce que je vous ai écrit en premier

Je vous joins le code en entier que j'ai fait avec l'enregistreur de macro :

pourquoi ? il est dans votre fichier

Le 21/03/2023 à 13:51

Bonjour Dan, je suis long à la détente, désolé.

C'est top

Merci beaucoup.

legreffier

Rechercher des sujets similaires à "tirer formule bas"