Détection première ligne vide

Le 12/09/2016 à 15:21

Bonjour à tous,

J'ai programmé un bouton sur VBA afin de pouvoir copier dans plusieurs tableaux à la suite certaines données provenant d'un tableau source. Ce tableau source change en fonction du fournisseur choisi (autre bouton afin de faire le choix). L'idée est de pouvoir cumuler les valeurs de plusieurs fournisseurs ou d'un même fournisseur (raison sociale qui aurait changé par exemple).

Pour cela le bouton "ajouter le fournisseur au cumul" cherche la première ligne vide de la colonne B pour ensuite copier les valeurs sources dans le tableau adjacent. Je pensais faire le cumul avec des simples fonctions SOMME dans un dernier tableau plus bas.

Ce bouton "Cumul" fonctionne parfaitement bien pour le premier cumul où il détecte bien la première ligne vide en B2 et colle comme il se doit les données dans le tableau adjacent. Ca se corse à partir du 2ème cumul (sans avoir effacé le premier cumul) : il détecte la première ligne vide en B11 alors que cette case reste toujours pleine ! Il devrait détecter en B13...

Je ne sais donc pas si j'utilise mal la fonction End(xldown) ou si elle n'est pas applicable dans mon cas. Merci d'avance pour votre aide. Je vous mets en PJ une version très allégée du fichier (les données sources sont figées, en fait elles dépendent normalement du fournisseur choisi) pour vous donner une idée du format et du code (une bonne partie est faite en enregistreur de macro par souci de temps, ne hurlez pas pour la syntaxe ou la surcharge inutile du code ).

J'espère avoir été clair dans mes explications, je suis évidemment disponible pour préciser si nécessaire.

Merci d'avance !

138excel-pratique.xlsm (189.15 Ko)
Le 12/09/2016 à 15:42

Bonjour,

Une fraction de code en venant du bas du tableau a65536 et en remontant vers la dernière ligne remplie et décalage de une ligne vers le bas

If MsgBox("Confirmez-vous L’insertion de ce nouveau contact ?", vbYesNo, "Demande de confirmation d’ajout ") = vbYes Then
L = Sheets("Feuill1").Range("a65536").End(xlUp).Row + 1 'Pour placer le nouvel enregistrement à la première ligne de tableau non vide

Amicalement

Pierrot

Le 12/09/2016 à 15:51

Bonjour hanz2.0

Ton code VBA ne peut pas fonctionner correctement ! Du moins pas comme tu le souhaites !

Explications :

Ta fonction (comme tu dis) End(xlDown) est correcte ! le problème ne vient pas de là !

Regardes dans ton onglet "CumulFRN" en colonne B

  1. première ligne "Ne pas effacer cette case" - ligne 1
  2. ligne suivante VIDE (en face du 1er FRN) - ligne 2
  3. lignes suivantes les éléments de ce FRN - lignes 3 à 12
  4. ligne suivante VIDE (en face du 2ème FRN) - ligne 13
  5. lignes suivantes les éléments de ce FRN - lignes 13 à 23
  6. etc...

Lorsque tu utilises End(xldown) c'est comme si tu utilises au clavier les touches [CTRL] + [FlecheBas] fais l'essai dans la colonne B et tu comprendras l'erreur !

Maintenant la solution ?

Soit

  1. tu évites les trous (cf. point 2, 4 précédent) en inscrivant une valeur bidon (cachée en Blanc/Blanc par exemple)
  2. tu ne recopies pas les libellés "mois année" et ainsi plus de ligne vide entre 2 FRN
  3. tu fais ton End(xlDown) sur une autre colonne - éventuellement cachée qui contient toujours quelque chose au fur et à mesure des "Cumul de FRN" impliquant évidement modification de ta macro-cmde !

(re) Bonjour Cappe Pierre

J'étais en cours de réponse lorsque tu as répondu ! Doublé

Juste une question :

Pourquoi utiliser

Cappe Pierre a écrit :

Range("a65536").End(xlUp).Row + 1

?

Certain ont plus de lignes que ça !

Pourquoi ne pas utiliser :

Cells(1,Rows.Count).End(xlUp).Row +1

?

Ainsi tu es (toujours) certain de commencer la recherche depuis la dernière ligne disponible et peu importe la version Excel dont tu disposes ! (et certainement même celles à venir ! sauf si Microsoft décide de supprimer Rows.Count évidement

Le 12/09/2016 à 16:06

Bonjour à tous les deux,

Merci pour vos réponses !

Merci Andrea pour m'avoir éclairé sur ce point je commençais à devenir fou et mes recherches là dessus ne menaient à rien du tout. Je vais tenter tes solutions, je garderai la plus simple et la plus efficace.

Cordialement.

Le 12/09/2016 à 16:15

(re)

De rien

à mon avis il faut utiliser

Ligne = Sheets("Cumul_FRN").Cells(1,Rows.Count).End(xlUp).Row +1

à la place de

Ligne = Sheets("Cumul_FRN").Range("B:B").End(xlDown).Row

C'est la plus efficace et aucune modification de la forme de ton tableau !

Le 12/09/2016 à 16:31

Bonjour, andrea73

Tu as raison, c'est une commande que j'ai trouvé dans un code

moi, je l'adapte à 1488576, pour excel 2010

mais ta version est surement la bonne, pas de risques d'erreurs.

Encore merci pour ta gentillesse

Amicalement

Pierrot

Le 12/09/2016 à 16:39

Je viens de tenter d'insérer ta ligne de code

Ligne = Sheets("Cumul_FRN").Cells(1,Rows.Count).End(xlUp).Row +1

mais VBA me renvoie ce message d'erreur : "Erreur définie par l'application ou par l'objet". Je ne vois pas bien ce qu'il faut adapter de ton code pour éviter cela...

Merci d'avance,

Hanz.

Le 12/09/2016 à 16:59

(re)

la bonne formulation est :

Ligne = Sheets("Cumul_FRN").Cells(Rows.Count, 1).End(xlUp).Row + 1

Mais pas :

Ligne = Sheets("Cumul_FRN").Cells(1,Rows.Count).End(xlUp).Row +1

Cependant c'est de ma faute

C'est moi qui ai fait l'erreur dans ma "discussion avec Pierre" (qui n'a même pas relevé d'ailleurs)

Explications de l'erreur :

Rows.Count := nombre de lignes d'une sélection (dans ce cas la feuille - donc 65535, 1048576... selon les versions)

Cells attend pour paramètre une LIGNE et une COLONNE (dans cet ordre) !

Si j'écris Cells(1,Rows.Count) je travaille sur la ligne 1 et sur la colonne de la dernière ligne disponible selon la version... cherchez l'erreur (sachant que dans ma version - en tout cas - je n'ai que 16384 colonnes mais pas 1048576 !)

Le 13/09/2016 à 08:53

Bonjour,

Merci pour la rectification, mais bien que cela fonctionne, ce n'est pas ce dont j'ai besoin malheureusement. En fait avec le end(xlup) le code me remplit le premier tableau disponible en partant du bas alors que je veux les remplir en partant du haut d'où mon obstination pour le end(xldown)

Je reprends mes tentatives avec les solutions de ta première réponse.

Cordialement.

Le 13/09/2016 à 09:24

Bonjour Hanz2.0

Bien que ce ne soit pas très logique de remplir un tableau

hanz2.0 a écrit :

en partant du haut

il n'y a aucun problème dans la solution "xlUp"...

Il suffit de la faire 2 fois :

  1. La première fois pour atteindre la dernière ligne du tableau
  2. La deuxième fois pour atteindre la première
  3. Avec éventuellement un ajout de 1 pour passer une ligne d'entête par exemple !

Je te remets "en garde" sur

hanz2.0 a écrit :

en partant du haut

il va falloir décaler vers le bas toutes les cellules !

Cappe Pierre

Cappe Pierre a écrit :

Bonjour, andrea73

Tu as raison, c'est une commande que j'ai trouvé dans un code moi, je l'adapte à 1488576, pour excel 2010

mais ta version est surement la bonne, pas de risques d'erreurs.

Encore merci pour ta gentillesse

Amicalement Pierrot

(De rien Pierrot !)

à mon avis il y a une solution alternative à ton

hanz2.0 a écrit :

en partant du haut

!
  1. Tu ajoutes "quand même" en bas de tableau mais :
  2. Dans une colonne cachée (éventuellement) tu inscris la date de l'ajout
  3. Et ensuite tu mets en place un tri permanent sur cette date (éventuellement en procédure évènementielle)
  4. Et voilà le tour est joué
      Pas de décalage !
    • Procédure "ajout" simplifiée !
    • Exécution plus rapide !
    • Logique Excel plus conforme !
    • Moins de code à saisir !
    • Etc...
Le 13/09/2016 à 09:50

Andrea,

Je ne suis pas tout à fait d'accord avec toi sur ce point

andrea73 a écrit :

Bien que ce ne soit pas très logique de remplir un tableau

hanz2.0 a écrit:en partant du haut

il n'y a aucun problème dans la solution "xlUp"...

Pour moi c'est l'inverse, on écrit sur une feuille en "partant du haut". Mais je suis entièrement d'accord avec toi la logique est plus que moyenne dans ce que je veux faire donc je risque surement de changer de méthode (je vais tenter celle que tu me proposes) et en dernier recours je me rabattrai sur un fastidieux enchainement de If et ElseIf

En tout cas la demande initiale est résolue, merci encore

Hanz.

Le 13/09/2016 à 10:04

(re)

Ok !

Un dernier conseil alors !

Il y a toujours une solution pour s'en passer ! et la lecture du code est beaucoup plus "logique "

Le 13/09/2016 à 11:53

Andrea,

Ne m'en veux pas trop les ElseIf ont été malheureusement retenus par le jury comme la solution la plus rapide mais aussi la plus moche 

Sub Cumul_FRN()

'cumuler les données de plusieurs FRN

Dim Ligne As Long

'________________TEST PREMIER TABLEAU________________

Sheets("Cumul_FRN").Select
If IsEmpty(Cells(2, 2)) Then

'copier nom fournisseur
Sheets("Indic_FRN").Select
Range("B8").Select
Selection.Copy

'coller nom fournisseur
Sheets("Cumul_FRN").Select
Cells(2, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'copier valeurs fournisseur sélectionné
Sheets("Indic_FRN").Select
Range("C9:AM18").Select
Application.CutCopyMode = False
Selection.Copy

'coller valeurs fournisseur sélectionné
Sheets("Cumul_FRN").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'________________TEST DEUXIEME TABLEAU________________

Sheets("Cumul_FRN").Select
ElseIf Cells(13, 2) = "" Then

'copier nom fournisseur
Sheets("Indic_FRN").Select
Range("B8").Select
Selection.Copy

'coller nom fournisseur
Sheets("Cumul_FRN").Select
Cells(13, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'copier valeurs fournisseur sélectionné
Sheets("Indic_FRN").Select
Range("C9:AM18").Select
Application.CutCopyMode = False
Selection.Copy

'coller valeurs fournisseur sélectionné
Sheets("Cumul_FRN").Select
Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'________________TEST TROISIEME TABLEAU________________

Sheets("Cumul_FRN").Select
ElseIf Cells(24, 2) = "" Then

'copier nom fournisseur
Sheets("Indic_FRN").Select
Range("B8").Select
Selection.Copy

'coller nom fournisseur
Sheets("Cumul_FRN").Select
Cells(24, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'copier valeurs fournisseur sélectionné
Sheets("Indic_FRN").Select
Range("C9:AM18").Select
Application.CutCopyMode = False
Selection.Copy

'coller valeurs fournisseur sélectionné
Sheets("Cumul_FRN").Select
Range("C25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'________________TEST QUATRIEME TABLEAU________________

Sheets("Cumul_FRN").Select
ElseIf Cells(35, 2) = "" Then

'copier nom fournisseur
Sheets("Indic_FRN").Select
Range("B8").Select
Selection.Copy

'coller nom fournisseur
Sheets("Cumul_FRN").Select
Cells(35, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'copier valeurs fournisseur sélectionné
Sheets("Indic_FRN").Select
Range("C9:AM18").Select
Application.CutCopyMode = False
Selection.Copy

'coller valeurs fournisseur sélectionné
Sheets("Cumul_FRN").Select
Range("C36").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'________________TEST CINQUIEME TABLEAU________________

Sheets("Cumul_FRN").Select
ElseIf Cells(46, 2) = "" Then

'copier nom fournisseur
Sheets("Indic_FRN").Select
Range("B8").Select
Selection.Copy

'coller nom fournisseur
Sheets("Cumul_FRN").Select
Cells(46, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'copier valeurs fournisseur sélectionné
Sheets("Indic_FRN").Select
Range("C9:AM18").Select
Application.CutCopyMode = False
Selection.Copy

'coller valeurs fournisseur sélectionné
Sheets("Cumul_FRN").Select
Range("C47").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End If

Columns("B:B").EntireColumn.AutoFit

End Sub

Voilà pour te donner une idée de l'horreur mais bon ca a le mérite de fonctionner et de faire ce que j'en veux 8)

Bonne journée !

Cordialement,

Hanz.

Le 13/09/2016 à 21:28

Bonjour hanz2.0

hanz2.0 a écrit :

les ElseIf ont été malheureusement retenus par le jury comme la solution la plus rapide mais aussi la plus moche

Mais bon l'essentiel (si tu arrive à te relire ) et qu'en plus ça fonctionne comme tu veux, c'est déjà ça !

Par contre un autre conseil :

tu devrais indenter ton code (je me suis arraché les yeux à le lire - et je pense que toi aussi)

en règle générale il faut utiliser une tabulation à chaque entrée dans une procédure/fonction, boucle, tests...

et diminuer d'une tabulation à chaque fois qu'un traitement (procédure/fonction, boucle, tests...) est terminé !

Par exemple ton code donnerait :

Sub Cumul_FRN()

'cumuler les données de plusieurs FRN

Dim Ligne As Long

    '________________TEST PREMIER TABLEAU________________

    Sheets("Cumul_FRN").Select
    If IsEmpty(Cells(2, 2)) Then

        'copier nom fournisseur
        Sheets("Indic_FRN").Select
        Range("B8").Select
        Selection.Copy

        'coller nom fournisseur
        Sheets("Cumul_FRN").Select
        Cells(2, 2).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        'copier valeurs fournisseur sélectionné
        Sheets("Indic_FRN").Select
        Range("C9:AM18").Select
        Application.CutCopyMode = False
        Selection.Copy

        'coller valeurs fournisseur sélectionné
        Sheets("Cumul_FRN").Select
        Range("C3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        '________________TEST DEUXIEME TABLEAU________________

        Sheets("Cumul_FRN").Select
        ElseIf Cells(13, 2) = "" Then

            'copier nom fournisseur
            Sheets("Indic_FRN").Select
            Range("B8").Select
            Selection.Copy

            'coller nom fournisseur
            Sheets("Cumul_FRN").Select
            Cells(13, 2).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

            'copier valeurs fournisseur sélectionné
            Sheets("Indic_FRN").Select
            Range("C9:AM18").Select
            Application.CutCopyMode = False
            Selection.Copy

            'coller valeurs fournisseur sélectionné
            Sheets("Cumul_FRN").Select
            Range("C14").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

            '________________TEST TROISIEME TABLEAU________________

            Sheets("Cumul_FRN").Select
        ElseIf Cells(24, 2) = "" Then

            'copier nom fournisseur
            Sheets("Indic_FRN").Select
            Range("B8").Select
            Selection.Copy

            'coller nom fournisseur
            Sheets("Cumul_FRN").Select
            Cells(24, 2).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

            'copier valeurs fournisseur sélectionné
            Sheets("Indic_FRN").Select
            Range("C9:AM18").Select
            Application.CutCopyMode = False
            Selection.Copy

            'coller valeurs fournisseur sélectionné
            Sheets("Cumul_FRN").Select
            Range("C25").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

            '________________TEST QUATRIEME TABLEAU________________

            Sheets("Cumul_FRN").Select
        ElseIf Cells(35, 2) = "" Then

            'copier nom fournisseur
            Sheets("Indic_FRN").Select
            Range("B8").Select
            Selection.Copy

            'coller nom fournisseur
            Sheets("Cumul_FRN").Select
            Cells(35, 2).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

            'copier valeurs fournisseur sélectionné
            Sheets("Indic_FRN").Select
            Range("C9:AM18").Select
            Application.CutCopyMode = False
            Selection.Copy

            'coller valeurs fournisseur sélectionné
            Sheets("Cumul_FRN").Select
            Range("C36").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

            '________________TEST CINQUIEME TABLEAU________________

            Sheets("Cumul_FRN").Select
        ElseIf Cells(46, 2) = "" Then

            'copier nom fournisseur
            Sheets("Indic_FRN").Select
            Range("B8").Select
            Selection.Copy

            'coller nom fournisseur
            Sheets("Cumul_FRN").Select
            Cells(46, 2).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

            'copier valeurs fournisseur sélectionné
            Sheets("Indic_FRN").Select
            Range("C9:AM18").Select
            Application.CutCopyMode = False
            Selection.Copy

            'coller valeurs fournisseur sélectionné
            Sheets("Cumul_FRN").Select
            Range("C47").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

    End If

    Columns("B:B").EntireColumn.AutoFit

End Sub

Rechercher des sujets similaires à "detection premiere ligne vide"