Copier et appliquer Formule jusqu'à dernière ligne renseignée d'une colonne

M

mattwarend

XLDnaute Junior
Bonjour à tous,

J'ai un fichier Excel et plus précisément une colonne G où j'applique la formule simple =NBCAR(A2).

Dans ma macro, je ne sais pas comment faire pour que cette formule soit exécutée automatiquement sur toutes les lignes renseignée de la colonne G (ligne susceptible de varier)
Genre copier formule vers le bas jusqu'à la dernière ligne renseignée.

Pouvez-vous SVP m'aider à ce sujet ?

Merci.
 
Trier par date Trier par votes
blord

blord

XLDnaute Impliqué
Re : Copier et appliquer Formule jusqu'à dernière ligne renseignée d'une colonne

Bonjour,

Voici un bout de code qui pourrait t'aider...

Dim DerLig As Long
DerLig = [G65000].End(xlUp).Row
[G2].AutoFill Destination:=Range("G2:G" & DerLig), Type:=xlFillDefault

En supposant que la formule est en G1

Blord
 
Vote positif 0 Vote négatif
M

mattwarend

XLDnaute Junior
Re : Copier et appliquer Formule jusqu'à dernière ligne renseignée d'une colonne

blord à dit:
Bonjour,

Voici un bout de code qui pourrait t'aider...

Dim DerLig As Long
DerLig = [G65000].End(xlUp).Row
[G2].AutoFill Destination:=Range("G2:G" & DerLig), Type:=xlFillDefault

En supposant que la formule est en G1

Blord
Cliquez pour agrandir...

Salut,

Merci pour ta réponse.
Voici mon code :

Code: 
Rows("1:4").Select
    Range("A4").Activate
    Selection.Delete Shift:=xlUp
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "NBCAR Name"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "NBCAR Path"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "NBCAR Name + Path"
    Range("A1:I1").Select
    Range("I1").Activate
    Selection.Font.Bold = True
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("L1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveCell.FormulaR1C1 = "TOTAL (Mo)"
    With ActiveCell.Characters(Start:=1, Length:=10).Font
        .Name = "Arial"
        .FontStyle = "Gras"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "TOTAL (Go)"
    Range("L1:M1,L3:M3").Select
    Range("L3").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("L3").Select
    Selection.Font.Bold = True
    Columns("C:C").Select
    Selection.Replace What:=" MB", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.NumberFormat = "0.0"
    Range("M1").Select
    Selection.NumberFormat = "0"
    Range("M3").Select
    Selection.NumberFormat = "0.0"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Size (Mo)"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "=SUM(C[-10])"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C/1000)"
    [B]Range("G2").Select
    ActiveCell.FormulaR1C1 = "=LEN(RC[-6])"[/B]
    Selection.AutoFill Destination:=Range("G2:G12"), Type:=xlFillDefault
    Range("G2:G12").Select
    [B]Range("H2").Select
    ActiveCell.FormulaR1C1 = "=LEN(RC[-6])"[/B]
    Selection.AutoFill Destination:=Range("H2:H12")
    Range("H2:H12").Select
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
    Selection.AutoFill Destination:=Range("I2:I12")
    Range("I2:I12").Select
    Range("A1").Select
    Columns("B:B").ColumnWidth = 22.14
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("A:I").Select
    Range("I1").Activate
    Selection.AutoFilter
    Range("A1").Select
    Columns("I:I").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("F:F").ColumnWidth = 8.29
    Columns("B:B").ColumnWidth = 20
    ActiveWindow.SmallScroll ToRight:=1
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.LargeScroll ToRight:=-1
    Range("A1").Select
    Columns("A:A").ColumnWidth = 21.43
    Range("A1").Select
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Range("A1").Select

En gras, les endroits où j'applique ma formule.
A quel emplacement dois-je mettre le code que tu m'as indiqué ?
 
Dernière édition:
Vote positif 0 Vote négatif
blord

blord

XLDnaute Impliqué
Re : Copier et appliquer Formule jusqu'à dernière ligne renseignée d'une colonne

Bonjour,

Tu peux mettre le code immédiatement après avoir placé ta formule dans tes cellules...

Par contre, il faut faire attention à un élément... Tu ne peux faire la validation de la dernière ligne DerLig = [G65000].End(xlUp).Row sur la colonne où tu veux recopier la formule. Il faut que tu valides la dernière ligne sur une colonne qui affiche réellement une valeur à la dernière ligne.

Par exemple, si tes données de la colonne A se rendent jusqu'à la ligne 145 et que les données de ta colonne G se rendent jusqu'à la ligne 34 et que tu valides la dernière ligne sur la colonne G, la formule sera recopiée uniquement pour les lignes 1 à 34....

Blord
 
Vote positif 0 Vote négatif
M

mattwarend

XLDnaute Junior
Re : Copier et appliquer Formule jusqu'à dernière ligne renseignée d'une colonne

Bonjour,

Merci pour les infos. J'ai donc modifié mon code comme suit (voir en gras) :

Code: 
Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 05/12/2008 par admincot
'
Rows("1:4").Select
    Range("A4").Activate
    Selection.Delete Shift:=xlUp
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "NBCAR Name"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "NBCAR Path"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "NBCAR Name + Path"
    Range("A1:I1").Select
    Range("I1").Activate
    Selection.Font.Bold = True
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("L1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveCell.FormulaR1C1 = "TOTAL (Mo)"
    With ActiveCell.Characters(Start:=1, Length:=10).Font
        .Name = "Arial"
        .FontStyle = "Gras"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "TOTAL (Go)"
    Range("L1:M1,L3:M3").Select
    Range("L3").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("L3").Select
    Selection.Font.Bold = True
    Columns("C:C").Select
    Selection.Replace What:=" MB", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.NumberFormat = "0.0"
    Range("M1").Select
    Selection.NumberFormat = "0"
    Range("M3").Select
    Selection.NumberFormat = "0.0"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Size (Mo)"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "=SUM(C[-10])"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C/1000)"
    Range("G2").Select
    [B]ActiveCell.FormulaR1C1 = "=LEN(RC[-6])"
    Dim DerLig As Long
    DerLig = [G65000].End(xlUp).Row
    [G2].AutoFill Destination:=Range("G2:G" & DerLig), Type:=xlFillDefault[/B]
    Range("G2:G12").Select
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=LEN(RC[-6])"
    Selection.AutoFill Destination:=Range("H2:H12")
    Range("H2:H12").Select
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
    Selection.AutoFill Destination:=Range("I2:I12")
    Range("I2:I12").Select
    Range("A1").Select
    Columns("B:B").ColumnWidth = 22.14
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("A:I").Select
    Range("I1").Activate
    Selection.AutoFilter
    Range("A1").Select
    Columns("I:I").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("F:F").ColumnWidth = 8.29
    Columns("B:B").ColumnWidth = 20
    ActiveWindow.SmallScroll ToRight:=1
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.LargeScroll ToRight:=-1
    Range("A1").Select
    Columns("A:A").ColumnWidth = 21.43
    Range("A1").Select
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Range("A1").Select
'
End Sub

Malheureusement, j'obtiens le message d'erreur :
La méthode AutoFill de la classe Range a échoué.
 
Vote positif 0 Vote négatif
K

kjin

XLDnaute Barbatruc
Re : Copier et appliquer Formule jusqu'à dernière ligne renseignée d'une colonne

Re
mattwarend à dit:
Code: 
Sub Macro1()
'....
    Dim DerLig As Long
    DerLig = [[COLOR="Red"][B]G[/B][/COLOR]65000].End(xlUp).Row
    [G2].AutoFill Destination:=Range("G2:G" & DerLig), Type:=xlFillDefault[/B]
'...
Malheureusement, j'obtiens le message d'erreur :
La méthode AutoFill de la classe Range a échoué.
Cliquez pour agrandir...

Si la dernière cellule non vide de ta plage de recopie est référencée par rapport à la colonne G, il n'est pas étonnant que ça bug puisqu'elle cette colonne est vide, non !?
Puisque la formule colonne G vérifie le NBCAR de la cellule A correspondante, c'est la dernière cellule non vide de la colonne A qui doit être la référence de ta plage de recopie

Code: 
'...
    [G2].FormulaR1C1 = "=LEN(RC[-6])"
    Dim DerLig As Long
    DerLig = [A65000].End(xlUp).Row
    [G2].AutoFill Destination:=Range("G2:G" & DerLig), Type:=xlFillDefault
'...

En outre, supprimmes (c'est un conseil) tous les "Select" de ton code qui sont inutiles dans la majorité des cas en VBA
A+
kjin
 
Vote positif 0 Vote négatif
M

mattwarend

XLDnaute Junior
Re : Copier et appliquer Formule jusqu'à dernière ligne renseignée d'une colonne

kjin à dit:
Re


Si la dernière cellule non vide de ta plage de recopie est référencée par rapport à la colonne G, il n'est pas étonnant que ça bug puisqu'elle cette colonne est vide, non !?
Puisque la formule colonne G vérifie le NBCAR de la cellule A correspondante, c'est la dernière cellule non vide de la colonne A qui doit être la référence de ta plage de recopie

Code: 
'...
    [G2].FormulaR1C1 = "=LEN(RC[-6])"
    Dim DerLig As Long
    DerLig = [A65000].End(xlUp).Row
    [G2].AutoFill Destination:=Range("G2:G" & DerLig), Type:=xlFillDefault
'...

En outre, supprimmes (c'est un conseil) tous les "Select" de ton code qui sont inutiles dans la majorité des cas en VBA
A+
kjin
Cliquez pour agrandir...

Merci !!!!!!!! Ça fonctionne parfaitement !
Excellent !
@+
 
Vote positif 0 Vote négatif
G

grenadine

XLDnaute Nouveau
Re : Copier et appliquer Formule jusqu'à dernière ligne renseignée d'une colonne

Bonjour,

Je voudrais étendre ma formule jusqu'à la dernière ligne de mon fichier -OK
Et ceux sur plusieurs colonnes (c'est la même formumle sur toute ces colonnes). Donc sur toute la longueur à partir de la seconde ligne des colonnes AS jusqu'à CE

J'ai essayé:
Code: 
'definition last cells not empty
Dim DerLig As Long
DerLig = [A65000].End(xlUp).Row
'extend formula to all line -KO
[as2].AutoFill Destination:=Range("as2:ce" & DerLig), Type:=xlFillDefault
J'ai un message d'erreur "La méthode AutoFill de la classe Range a échoué."
Y-at'il un paramètre magique que je pourrais activé ou je dois dupliqué la formule pour chaque colonne?

Merci.
Grenadine
 
Vote positif 0 Vote négatif
Modeste geedee

Modeste geedee

XLDnaute Barbatruc
Re : Copier et appliquer Formule jusqu'à dernière ligne renseignée d'une colonne

Bonsour®
grenadine à dit:
Je voudrais étendre ma formule jusqu'à la dernière ligne de mon fichier -OK
Et ceux sur plusieurs colonnes (c'est la même formumle sur toute ces colonnes). Donc sur toute la longueur à partir de la seconde ligne des colonnes AS jusqu'à CE

J'ai essayé:
Code: 
'definition last cells not empty
Dim DerLig As Long
DerLig = [A65000].End(xlUp).Row
'extend formula to all line -KO
[as2].AutoFill Destination:=Range("as2:ce" & DerLig), Type:=xlFillDefault
J'ai un message d'erreur "La méthode AutoFill de la classe Range a échoué."
Y-at'il un paramètre magique que je pourrais activé ou je dois dupliqué la formule pour chaque colonne?

Merci.
Grenadine
Cliquez pour agrandir...

[as2:CE2].AutoFill Destination:=Range("as2:ce" & DerLig), Type:=xlFillDefault
 
Vote positif 0 Vote négatif

Discussions similaires

A
  • Résolu(e)
Microsoft 365 VBA Etendre formule jusqu'à la dernière ligne dans VBA
Réponses
13
Affichages
332
AD95
A
A
  • Question
XL 2021 Générer des données à partir d'une ligne spécifique jusqu'à une autre
Réponses
5
Affichages
170
gbinforme
G
L
  • Résolu(e)
XL 2019 Renvoyer une valeur colonne A avec la dernière valeur de la colonne B
Réponses
5
Affichages
325
Gégé-45550
Gégé-45550
X
  • Question
XL 2016 Copier à partir de la dernière ligne renseignée.
Réponses
8
Affichages
478
Xender
X
C
  • Question
Autres Ajouter formule dans une colonne en VBA
Réponses
4
Affichages
465
job75
job75
G
  • Résolu(e)
XL 2013 copier coller valeur d'une colonne dynamique
Réponses
13
Affichages
425
gwenn49
G
D
  • Question
XL 2021 Copier coller des colonnes suivant le nom de la colonne et non pas le N° de colonne idem ligne
Réponses
8
Affichages
453
desmonts
D
B
  • Question
XL 2013 Automatiser une formule sur les nouvelles lignes insérées en fonction d'une recherche sur les enregistrements
Réponses
9
Affichages
288
bedley
B
Doods
  • Résolu(e)
Microsoft 365 Renvoi de la dernière valeur d'une ligne selon condition
Réponses
6
Affichages
386
job75
job75
M
  • Résolu(e)
Microsoft 365 SOMPROD et difficulté à trouver résultat suivants plusieurs critères
Réponses
7
Affichages
262
mcfv
M

Membres actuellement en ligne

16 membres et 135 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 697
Messages
2 091 077
Membres
104 753
dernier inscrit
FLIS