VBA Excel: Etendre une formule jusqu'à la dernière ligne

Résolu/Fermé

Formule

Alice - 10 déc. 2013 à 11:12
PhilDk Messages postés 20 Date d'inscription mardi 26 août 2008 Statut Membre Dernière intervention 18 août 2014 - 18 août 2014 à 10:43

Bonjour,

Je cherche tout simplement un code VBA pour étendre une formule automatiquement de la cellule C6 à la dernière de la ligne sans la citer.
Actuellement, le code suivant fonctionne très bien:

Range("C6").Select

Selection.Autofill Destination:=Range("C6:C450")

Mais comme la dernière cellule n'est pas tjr la 450ème, j'aimerais qu'il la définisse automatiquement et au coup par coup.

Merci à vous!

A voir également:

Proposez une formule comportant une seule fonction et à recopier vers le bas dans la colonne a du fichier à télécharger.
Virginie organise un rallye avec 30 équipes. elle veut créer un code pour désigner chaque équipe. elle a commencé à la main, mais elle voudrait le faire calculer à l'aide d'une formule. proposez une formule comportant une seule fonction et à recopier vers le bas dans la colonne a du fichier à télécharger. quelle formule sera en a9 ? - Meilleures réponses
Virginie organise un rallye avec 30 équipes. elle veut créer un code pour désigner chaque équipe. elle a commencé à la main, mais elle voudrait le faire calculer à l'aide d'une formule. proposez une formule comportant une seule fonction et à recopier vers le bas dans la colonne a du fichier à télécharger. - Meilleures réponses
Liste déroulante excel - Guide
Si et excel - Guide
Aller à la ligne excel - Guide
Word et excel gratuit - Guide
Mise en forme conditionnelle excel - Guide

4 réponses

Réponse 1 / 4

pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 751
10 déc. 2013 à 11:56

Bon.
Il nous suffit donc maintenant, de remplacer dans

Destination:=Range("C6:C450")

, 450 par la variable DernLigne.
Profitons en pour supprimer ces .Select et autres Selection inutiles...
Exemple :

Dim DernLigne As Long

DernLigne = Range("C" & Rows.Count).End(xlUp).Row
Range("C6").AutoFill Destination:=Range("C6:C" & DernLigne)

Auparavant, cependant, on cherche depuis tout à l'heure la dernière ligne non vide de la colonne C. Or, dans la colonne C, on cherche à remplir de formule... Donc, pour l'instant, la colonne C ne contient rien, si je ne m'abuse... Donc, cela n'a pas de sens de chercher la dernière ligne non vide de la colonne C.

Alice
10 déc. 2013 à 17:04

Afin de tout remettre au clair, j'ai entré le code suivant:

Sub Extension_formule()

Dim DernLigne As Long

DernLigne = Range("C" & Rows.Count).End(xlUp).Row

Range("C6").AutoFill Destination:=Range("C6:C" & DernLigne)

End Sub

Il en est ressorti un message d'erreur, à savoir: "la méthode Autofill de la classe range a échoué".

Par rapport à votre remarque qui suit, j'ai pensé qu'en répondant 450 à votre question de connaître le nombre s'affichant dans la boîte de dialogue en entrant le code suggéré en début de forum, à savoir:

Sub Test()
Dim DernLigne As Long
DernLigne = Range("C" & Rows.Count).End(xlUp).Row
MsgBox DernLigne
End Sub

vous en déduiriez aisément que la fameuse colonne C se trouve dans un tableau de 450 lignes. C'est d'ailleurs toute l'utilité d'étendre une formule...

pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 751
11 déc. 2013 à 07:56

Bonjour,

En fait, si vous souhaitez étendre votre formule de la ligne 6 à la ligne 450, c'est à priori que votre colonne C ne contient rien, à la base.
Ou alors vous avez des données (ou formules) que vous souhaitez remplacer.

Dans le premier cas (colonne C vide), il faut calculer la dernière ligne en fonction d'une autre colonne. Par exemple, la colonne B elle, peut être remplie.
Dans ce cas, le code devrait être celui-ci :

Sub Extension_formule()
Dim DernLigne As Long

DernLigne = Range("B" & Rows.Count).End(xlUp).Row
Range("C6").AutoFill Destination:=Range("C6:C" & DernLigne)
End Sub

Dans le second cas, si votre colonne C est déjà remplie, le code donné précédemment est censé fonctionner.
Sauf si quelque chose l'en empêche...
Mais là, il s'agit d'une particularité de votre classeur que nous ne pouvons connaitre sans le voir...

J'ai fait des tests, et arrive à reproduire l'erreur "la méthode Autofill de la classe range a échoué", lorsque la colonne C ne contient qu'une formule en C6 et rien d'autre...

Retente avec le calcul de la dernière ligne d'une autre colonne et, si cela ne fonctionne toujours pas, fait nous passer un classeur exemple grâce à cjoint.com.

Réponse 2 / 4

pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 751
11 déc. 2013 à 16:04

Voici un code, qui devrait fonctionner, A ADAPTER, lis bien les commentaires...

Sub Sans_score11()
Dim i As Integer, drLig As Long

Application.ScreenUpdating = False
  
  'boucle de 4 au nombre de feuilles du classeur
  For i = 4 To Sheets.Count
    'dans la feuille numéro i
    With Sheets(i)
      'on insère une colonne entre B et C
      .Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
      'on y inscrit "Sans score" en C5
      .Range("C5").Value = "Sans score"
      'On inscrit la formule en C6
      .Range("C6").FormulaR1C1 = "=COUNTIF(C[9],""*score*"")"
      
      '*******************************************************************************************
      'Ici, tu dois déterminer qu'elle est la colonne te permettant de trouver
      'la dernière ligne.
      'La colonne C, nouvellement créée, se termine en C6. Donc pas valable
      'La colonne B? L?, à toi de voir et de remplacer dans la ligne suivante :
      '*******************************************************************************************
      drLig = .Range("B" & Rows.Count).End(xlUp).Row
      
      
      'si drLig est inférieure ou égale à 6 la méthode autofill va planter
      'donc on teste drLig
      If drLig <= 6 Then
        'si drLig est inférieur ou égal à 6, message à l'utilisateur :
        MsgBox "La dernière ligne non vide de cette colonne (feuille " & i & ") est inférieure ou égale à 6. Merci de vérifier"
        'on sélectionne la feuille incriminée
        Sheets(i).Select
        'on stoppe et sort de la procédure
        Exit Sub
      Else 'SINON on applique l'autofill
        .Range("C6").AutoFill Destination:=.Range("C6:C" & drLig)
      End If
    End With
  Next i
Application.ScreenUpdating = True
End Sub

Alice
11 déc. 2013 à 16:28

Merci pour le code, qui fonctionne à merveille!

Et si tu souhaites relever de nouveaux défis de ce type, j'en ai quelques autres (du même niveau je pense)!

Merci encore!

pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 751
11 déc. 2013 à 16:30

Et si tu souhaites relever de nouveaux défis de ce type, j'en ai quelques autres (du même niveau je pense)!

ça dépends. C'est pour l'école?

Alice
11 déc. 2013 à 16:38

Non, je ne suis pas étudiante, et encore moins en informatique, sinon je n'aurais pas de telles difficultés.
C'est simplement pour m'épargner des tâches fastidieuses en utilisant à bon escient les outils créés à cette fin. Mais pour cela, il faut des connaissances que j'essaye d'acquérir, en attendant, je viens sur ce forum.

pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 751
11 déc. 2013 à 16:51

Il faut des connaissances que j'essaye d'acquérir,
C'est tout à ton honneur...
en attendant, je viens sur ce forum.
Et bien continue, tu es la bienvenue!

A+ n'hésite surtout pas...

Réponse 3 / 4

dlt-pan Messages postés 481 Date d'inscription jeudi 17 mai 2007 Statut Membre Dernière intervention 23 décembre 2014 67
10 déc. 2013 à 11:32

bonjour
essaie :
Range("C6").Select
nbligne = Range("C65500").End(xlUp).Row
Selection.Autofill Destination:=Range(cells(6,3),cells(nbligne,3))

Alice
10 déc. 2013 à 11:41

Bonjour,

Merci pour la réponse.
Mais lorsque je saisis le code, le message d'erreur 1004 suivant apparaît: "erreur définie par l'application ou par l'objet". Une idée?

Alice
10 déc. 2013 à 11:42

Puis le message suivant: "la méthode Autofill de la classe range a échoué"

Réponse 4 / 4

pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 751
10 déc. 2013 à 11:15

Bonjour,

Pour trouver, par VBA, la dernière ligne non vide d'une colonne, vous pouvez jeter un oeil à cette astuce CCM...

Alice
10 déc. 2013 à 11:24

Bonjour,

Merci pour le lien mais c'est un peu du chinois pour moi.
Je veux juste étendre ma formule de la cellule C6 à la dernière de mon tableau située tjr en colonne C.

Merci

pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 751
10 déc. 2013 à 11:28

Avez vous au moins pris le temps de :
- lire l'astuce?
- la tester?
.........
Testez déjà ceci dans votre classeur :

Sub Test()
Dim DernLigne As Long 
DernLigne = Range("C" & Rows.Count).End(xlUp).Row
MsgBox DernLigne
End Sub

Alice
10 déc. 2013 à 11:34

J'ai bel et bien pris le temps de la lire et de la tester mais étant novice en VBA, je ne suis pas parvenue à l'adapter à mon cas.
Je vous remercie pour votre réponse, mais je ne vois pas où figure la cellule C6 à partir de laquelle je souhaite étendre ma formule; avez-vous pris le temps de lire la question?

Merci

pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 751
10 déc. 2013 à 11:38

Bien sur que j'ai lu votre question, sinon je n'y répondrais pas.
Je ne veux pas vous donner la solution "toute cuite" car cela ne vous servirait à rien...
C6 n'est pas importante dans l'histoire car c'est une cellule que l'on connait.
Ce que l'on veux connaitre c'est le numéro de la dernière ligne non vide de la colonne C; pour pouvoir étendre notre formule jusqu'à elle.
Non?
Donc, essayez la macro de test que je vous ai donné ci-dessus, elle devrait vous donner ce qu'il vous faut.
On l'adaptera à autofill après vos tests.

Alice
10 déc. 2013 à 11:49

La macro m'affiche le numéro de la dernière ligne, autrement dit le 450 sus-menstionné que j'aimerai, si possible, ne pas avoir saisir à chaque fois que je lance la macro.

Merci

Discussions similaires

Reproduire formule jusqu'en bas

recopie de formule vers la droite et la gauche

Etendre une formule jusqu'à la fin du fichier

je veut créer des codes

Glisser une formule avec $

Formule excel pour trouver un libellé correspondant à un code