VBA: Ajouter une ligne en fin de tableau en reprenant certaines données de la dernière ligne

[Mugiwara]

Bonjour,

Bien que trouvé pas mal de sujets sur mon pb, je ne suis pas parvenu à le résoudre. Je m'explique:

Je souhaite constituer une base de données (tableau avec mise en forme) . L'idée est d'avoir un bouton pour diverses actions (ajout de ligne avec certaines infos / ajout de ligne en copiant les infos précédentes) .

En premier lieu je souhaite une macro permettant d'ajouter en fin de tableau, une nouvelle ligne (qui garde la mise en forme tableau) qui doit faire apparaitre et figer dans la 1ère cellule de cette nouvelle ligne, la date du jour (colonne B ds mon fichier) + appliquer la formule de la colonne C + générer un n° en colonne D qui correspond au n° de la ligne précédente (même colonne) auquel on ajouterai 1 (exemple si 6014 en D21, la nouvelle ligne générée devra faire apparaitre en D22 le nombre 6015) + un texte prédéfini en colonne H et laisser le reste des champs vides.

La seconde macro consisterait de la même manière à générer une nouvelle ligne en fin de tableau, toujours avec la notion de date du jour en colonne B, formule en colonne C, texte prédéfini en colonne H, de recopier toutes les autres infos à l'exception des cellules des colonnes I, P, Q et R qui devront rester vides.

J'espère avoir été assez clair pour vous permettre de comprendre mon besoin et ainsi pouvoir m'aider. Je vous remercie par avance, je joins mon ébauche de fichier pour mieux comprendre...

 

[Hieu]

Salut,

Pas bien compris la requête ; j'ai corrigé (?) les codes déjà présents ?

Sub Suppression()
ligne = Range("B65000").End(xlUp).Row
Rows(ligne).Delete
End Sub
Sub nouvelle_ligne()
ligne = Range("B65000").End(xlUp).Offset(1, 0).Row
Range("b" & ligne) = Now
Range("d" & ligne) = Range("d" & ligne - 1) + 1
End Sub

 

[Mugiwara]

Bonsoir Hieu,

Déjà merci te t'être penché sur mon cas et d'avoir corrigé ce qui était mal fait...

Sur le code que tu me proposes, la date est avec la formule "now" ce qui empêche mon vlookup, j'ai donc souhaité la remplacer par la formule "today" mais ça n'a pas l'air de fonctionner...si tu as la solution pour ce point, je suis preneur

Autrement pour reprendre ma requête, le code que tu m'as transmis "nouvelle ligne" correspondrait au bouton "nouveau dossier" et répond pratiquement au besoin (juste l'histoire de la date au format dd/mm/aaaa au lieu de la date + heure et je souhaiterais en plus qu'en colonne H soit inscris une phrase type "Saisir non-conformité") ...
Et ensuite il me manquerait la macro pour le bouton "ajouter anomalie" qui ressemble à la précédente, mais qui copierait la majeure partie des infos de la dernière ligne avant ajout, mais pas toutes...
En gros date en colonne C et texte "Saisir non-conformité" en colonne H pareil que l'autre code, toutes les autres infos identiques à la ligne du dessus, cellules correspondant aux colonnes I, P, Q et R doivent en revanche rester vides...

Et au cas ou tu te poserais la question, le bouton jaune (ajouter ligne) ne m'est d'aucune utilité, c'était justement pour m'exercer...

En espérant être un peu plus clair

 

[Mugiwara]

Pour la date c'est bon, pb résolu, il suffit d'ajouter le format:

Sub ADD_ROW()
'
' Ajout d'une ligne à la fin du tableau
'
ligne = Range("B65000").End(xlUp).Offset(1, 0).Row
Range("b" & ligne) = Format(Now, "mm/dd/yyyy")
Range("d" & ligne) = Range("d" & ligne - 1) + 1
End Sub

 

[Mugiwara]

Bon bah finalement, avec un peu de réflexion j'ai obtenu le résultat souhaité, merci encore de ton aide.

 

[TooFatBoy]

Bonjour,

Zut, j'arrive trop tard... J'allais te proposer une macro pour ajouter une ligne en fin de tableau pour un nouveau dossier.
Je mets tout de même la macro, au cas où :

Sub NouveauDossier()
  With Sheets("DATABASE").ListObjects("Tableau_DATABASE")
    .ListRows.Add AlwaysInsert:=True
    .ListColumns("Date").DataBodyRange(.ListRows.Count) = Date
    .ListColumns("N° Dossier").DataBodyRange(.ListRows.Count) = .ListColumns("N° Dossier").DataBodyRange(.ListRows.Count - 1) + 1
    .ListColumns("Non-conformité").DataBodyRange(.ListRows.Count) = .ListColumns("N° Dossier").DataBodyRange(.ListRows.Count) & "Texte prédéfini..."
  End With
End Sub

 

[TooFatBoy]

A mon avis, dans le fichier que tu as fourni, le tableau bleu de la feuille "Settings" ne sert à rien.

Ou du moins dans le fichier tel que tu l'as fourni, car ce tableau semble être utilisé (actuellement) uniquement pour trouver le numéro de semaine de la date entrée en colonne B de la feuille "DATABASE" et inscrire se numéro en colonne C de la feuille "DATABASE".

Or tu peux remplacer ta formule de recherche par une formule Excel donnant directement le numéro de semaine pour une date donnée.
Ainsi, en C5 de la feuille "DATABASE", tu peux tester cette formule :
=NO.SEMAINE(B5;21)

 

[TooFatBoy]

Pour la deuxième macro, j'ai utilisé exactement le même principe :

Sub AjoutAnomalie()
  With Sheets("DATABASE").ListObjects("Tableau_DATABASE")
    .ListRows.Add AlwaysInsert:=True
    .ListColumns("Date").DataBodyRange(.ListRows.Count) = Date
   .ListColumns("N° Dossier").DataBodyRange(.ListRows.Count) = .ListColumns("N° Dossier").DataBodyRange(.ListRows.Count - 1)
   .ListColumns("Modèle camion/nacelle").DataBodyRange(.ListRows.Count) = .ListColumns("Modèle camion/nacelle").DataBodyRange(.ListRows.Count - 1)
   .ListColumns("Chef d'équipe").DataBodyRange(.ListRows.Count) = .ListColumns("Chef d'équipe").DataBodyRange(.ListRows.Count - 1)
   .ListColumns("Camion double").DataBodyRange(.ListRows.Count) = .ListColumns("Camion double").DataBodyRange(.ListRows.Count - 1)
   .ListColumns("Non-conformité").DataBodyRange(.ListRows.Count) = .ListColumns("N° Dossier").DataBodyRange(.ListRows.Count) & "Texte prédéfini..."
   .ListColumns("Chef de pont").DataBodyRange(.ListRows.Count) = .ListColumns("Chef de pont").DataBodyRange(.ListRows.Count - 1)
   .ListColumns("Monteur").DataBodyRange(.ListRows.Count) = .ListColumns("Monteur").DataBodyRange(.ListRows.Count - 1)
   .ListColumns("Bleu").DataBodyRange(.ListRows.Count) = .ListColumns("Bleu").DataBodyRange(.ListRows.Count - 1)
   .ListColumns("Controleur").DataBodyRange(.ListRows.Count) = .ListColumns("Controleur").DataBodyRange(.ListRows.Count - 1)
   .ListColumns("Klubb FR").DataBodyRange(.ListRows.Count) = .ListColumns("Klubb FR").DataBodyRange(.ListRows.Count - 1)
   .ListColumns("Opérateur Qualité").DataBodyRange(.ListRows.Count) = .ListColumns("Opérateur Qualité").DataBodyRange(.ListRows.Count - 1)
  End With
End Sub

 

[Mugiwara]

Bonjour Marcel,

Merci également à toi, je vais quand même garder tout çà sous le coude et surtout effectivement, je vais plutôt utiliser la formule pour le N° de semaine, merci pour l'astuce...comme quoi on en apprend tous les jours

Mon cerveau ayant continué à tourner cette nuit, j'ai un autre besoin, que je ne suis pas certain de pouvoir correctement formaliser:

Est-il possible de produire une macro qui aurait pour action de rechercher un N° de dossier (à la manière d'un Ctrl + F) et qui se ferait en renseignant une cellule par le N° de dossier recherché?
Il faudrait que la cellule trouvée soit la dernière comportant ce n° de dossier.
Ensuite il s'agirait d'avoir un nouveau bouton pour y insérer une ligne à la suite de la dernière valeur trouvée... (même macro que la dernière que tu as publié mais insertion de ligne plutôt qu'ajout en fin de tableau).

Merci d'avance.

 

[Jean-Eric]

Bonjour,
Ma petite contribution pour pour les points 1 et 2.
Cdlt.

Option Explicit

Dim lo As ListObject
Dim lr As ListRow
Dim n As Long

'Point 1
Public Sub InsertRowInTable_1()
    Application.ScreenUpdating = False
    Set lo = ActiveSheet.ListObjects(1)
    Set lr = lo.ListRows.Add(AlwaysInsert:=True)
    n = lo.ListRows.Count
    With lr.Range
        .Cells(1, 1).Value = Date
        .Cells(1, 3).Value = lo.Range.Cells(n, 3) + 1
        .Cells(1, 7).Value = Format(n, "00") & " - Saisir non-conformité"
    End With
    Set lr = Nothing: Set lo = Nothing
End Sub

'Point 2
Public Sub InsertRowInTable_2()
Dim tbl As Variant, v As Variant
    Application.ScreenUpdating = False
    tbl = Array(4, 5, 6, 9, 10, 11, 12, 13, 14)
    Set lo = ActiveSheet.ListObjects(1)
    Set lr = lo.ListRows.Add(AlwaysInsert:=True)
    n = lo.ListRows.Count
    With lr.Range
        .Cells(1, 1).Value = Date
        .Cells(1, 3).Value = lo.Range.Cells(n, 3) + 1
        .Cells(1, 7).Value = Format(n, "00") & " - Saisir non-conformité"
        For Each v In tbl
            .Cells(1, v).Value = lo.Range.Cells(n, v)
        Next
    End With
    Set lr = Nothing: Set lo = Nothing
End Sub
'Réinitialisation tableau en conservant les formules
'et la mise en forme.
Public Sub ResetTable()
    Application.ScreenUpdating = False
    Set lo = ActiveSheet.ListObjects(1)
    If Not lo.DataBodyRange Is Nothing Then lo.DataBodyRange.Delete
    Set lo = Nothing
End Sub

 

[Mugiwara]

Bonjour Jean-Eric, et merci pour ta contribution ... du coup je constate que vous êtes 3 à m'avoir solutionner mon besoin...de 3 façons différentes... N'est-ce pas étonnant de parvenir strictement au même résultat en demandant quasi les même actions mais en ayant une syntaxe qui diffère pas mal?

 

[Jean-Eric]

Re,
Il y a en effet plusieurs manières d'appréhender la chose !
Pour ton dernier point, un peu confus, je te propose cette procédure.
A te relire.
Cdlt.

Public Sub Lookup()
Dim Msg As String, Title As String, Answer As String, firstAddress As String
Dim rCell As Range
Dim nn As Long
    Application.ScreenUpdating = False
    Msg = "Veuillez saisir le numéro de dossier"
    Title = "Recherche"
    Answer = InputBox(Msg, "title")
    If Answer = "" Then Exit Sub
    Set lo = ActiveSheet.ListObjects(1)
    'With lo
        '.Sort.SortFields.Add .ListColumns(1).DataBodyRange, xlSortOnValues, xlAscending
        '.Sort.SortFields.Add .ListColumns(3).DataBodyRange, xlSortOnValues, xlAscending
        '.Sort.Apply
        '.Sort.SortFields.Clear
    'End With
    With lo.ListColumns(3).DataBodyRange
        Set rCell = .Find(what:=Answer, LookIn:=xlValues, lookat:=xlWhole)
        If Not rCell Is Nothing Then
            firstAddress = rCell.Address
            n = rCell.Row
            Do
                If rCell.Row > n Then n = rCell.Row
                Set rCell = .FindNext(rCell)
            Loop While Not rCell Is Nothing And rCell.Address <> firstAddress
        End If
    End With
    n = n - lo.HeaderRowRange.Row
    Set lr = lo.ListRows.Add(n + 1)
    nn = lo.ListRows.Count
    With lr.Range
        .Cells(1, 1).Value = Date
        .Cells(1, 3).Value = lo.Range.Cells(n - 1, 3)
        .Cells(1, 7).Value = Format(nn, "00") & " - Saisir non-conformité"
    End With
    Set rCell = Nothing: Set lr = Nothing: Set lo = Nothing
End Sub

 

[TooFatBoy]

du coup je constate que vous êtes 3 à m'avoir solutionner mon besoin...de 3 façons différentes... N'est-ce pas étonnant de parvenir strictement au même résultat en demandant quasi les même actions mais en ayant une syntaxe qui diffère pas mal?

Hieu a utilisé le fait que lorsque on inscrit des données dans la première ligne située en-dessous d'une Table Excel, cette ligne devient alors partie intégrante de ladite Table.

Perso, j'ai aussi utilisé le fait que ton tableau soit une Table Excel mais pour utiliser des adressages typiques d'une Table Excel (avec les noms des en-tête des colonnes par exemple).

Jean-Eric a utiliser à peu près la même méthode que moi, mais en utilisant les numéros des colonnes plutôt que leur nom.
Non pas pour critiquer (loin de moi cette idée !) mais juste pour t'expliquer la différence entre l'utilisation des numéros de colonnes et l'utilisation du nom de celles-ci : si tu déplaces tes colonnes et donc en change l'ordre, avec l'utilisation les numéros de colonnes tu adresseras toujours la même colonne de la feuille Excel (sauf si tu viens modifier ta macro en conséquence), alors qu'en utilisant les noms des colonnes ça adressera toujours la bonne colonne (celle portant le nom donné) même sans modifier le code de la macro.

D'où les trois macros différentes.
Mais en règle générale, tu auras toujours autant de macros différentes que tu as de codeurs différents.

 

[Mugiwara]

Bonjour à tous,

Merci pour vos retours instructifs!! Jean-Eric, le dernier code me convient très bien, et au pire je devrais être en mesure de l'ajuster à ma guise.
Donc un grand merci pour votre aide.

Après quelques tests et manipulations, je me rends compte (quelque soient mes macros) qu'il s'agisse d'une insertion en milieu de tableau ou d'un ajout en fin, même si la macro génère bien l'action souhaitée, le curseur reste ou il était (sélection de la cellule sur nouvelle ligne ne se fait pas)... en gros si je suis à la première ligne de mon tableau quand je décide d'ajouter une ligne en fin, et même si mon tableau comporte 1000 lignes, l'affichage se maintient sur la 1ere ligne même si la 1001 a bien été ajoutée. Est-il possible de se positionner automatiquement sur la ligne venant d'être ajoutée/insérée?

 

[TooFatBoy]

Dans mes macros (même si j'ai bien compris que tu ne les utilises pas, mais tu pourras t'inspirer de cette réponse) tu dois pouvoir ajouter, juste avant la ligne "End With", un truc du genre
ListColumns("N° Dossier").DataBodyRange(.ListRows.Count).select
Mettre évidemment le nom de la colonne désirée.