[VBA] insérer une ligne en copiant les formules de la ligne précédente

Le 17/09/2018 à 21:07

Bonsoir à tous

Tout d'abord je voudrai vous remercier pour l'aide que vous proposez en ligne, c'est TOPISSIME !

Je souhaiterai optimiser un tableau de suivi de stocks qui comporte beaucoup de lignes et beaucoup de conditions. Pour que mon sujet soit plus compréhensible, j'ai volontairement simplifier la pièce jointe.

Vous trouverez en pièce jointe le fichier :

105tableau-de-suivi-des-stocks.xlsm (16.90 Ko)

Explication du fichier et de l'optimisation souhaitée:

Je souhaiterai insérer une ligne dans mon tableau (feuille: Suivi des stocks) en respectant plusieurs conditions:

- si je choisi "Machine" dans la liste déroulante (B5) et "Perceuse à colonne" dans la liste déroulante (C5), puis que je clique sur le bouton "Insérer une ligne" (D5), alors une nouvelle ligne est insérée (nouvelle ligne entre la ligne 20 et 21).

Cette nouvelle ligne doit :

comporter "Machine" en B21 et "Perceuse à colonne" en C21
être vide pour les autres cellules mais comporter les formules des cellules du dessus (H20 ; I20 ; L20 ; M20 ; P20 ; Q20 ; T20).

J'espère que ma demande est compréhensible. Si ce n'est pas le cas, je m'en excuse d'avance.

Et si quelqu'un aurait une piste, je le remercie d'avance.

Le 18/09/2018 à 07:36

bonjour

dans une gestion de données, on fait plus simple :

menu Accueil, mettre sous forme de tableau (ainsi Excel crée les formules automatiquement de ligne en ligne)
on ajoute une nouvelle ligne en pied de Tableau (ainsi Excel...)

note : ne pas présenter en blocs par mois

faire une table simple, avec les colonnes suivantes

date (format dd/mm/aaaa ou aa)
machine
outil
quantité (en + si entrée, - si sortie)

fini !

et si besoin on trie selon les colonnes Machine et Outil, mais habituellement on n'en n'a pas besoin.

le stock c'est une addition de toutes les entrées moins toutes les sorties, on le calcule dans un TCD

note 2 : en gros, on a un onglet de saisie des mouvements E et S, et un onglet avec TCD pour voir évoluer le stock.

on peut ajouter des bilans, des courbes, un colonne € pour bilans coûts etc.

note 3 : pas de VBA, et aucune formule non plus. Magie d'Excel qui fait tout tout seul

bon travail

Le 18/09/2018 à 14:44

Bonjour jmd,

Merci pour ta réponse. Mais, je souhaite que ça soit réalisé avec du VBA.

Je vais continuer de cherche de mon côté.

Cordialement

Monsach

Le 18/09/2018 à 16:25

re

pourquoi du VBA ?

Le 18/09/2018 à 17:27

Bonjour,

Une proposition VBA avec conversion de ton tableau en objet tableau pour répétition des formules, comme recommandé par JMD.

219tableau-de-suivi-des-stocks1.xlsm (31.22 Ko)

Le 18/09/2018 à 19:15

jmd a écrit :
re
pourquoi du VBA ?

Re jmd,

Je préfère du VBA pour plusieurs raisons:

une dizaine de personnes vont utiliser le fichier (donc chacun risque de remplir le fichier à sa façon, et donc pas facile de l'exploiter après)
le fichier sera protégé pour éviter que les personnes ne fassent de mauvaises frappes (donc ils ne pourront pas ajouter de lignes ou en supprimer via le clic droit/insérer)

Grâce au bouton pour insérer une ligne, je suis sûr qu'il n'y aura pas de cellules avec des fonctions mal recopiées.

Pour des raisons de confidentialité je ne peux pas mettre le vrai fichier Excel, du coup j'ai schématisé au maximum. A moi, par la suite d'adapter les réponses que vous me faîtes à mon vrai fichier.

Cordialement

Monsach

Le 18/09/2018 à 19:25

thev a écrit :
Bonjour,
Une proposition VBA avec conversion de ton tableau en objet tableau pour répétition des formules, comme recommandé par JMD.

Bonjour thev,

C'est super, tu as compris mon besoin du premier coup!! Je te remercie!

En revanche il y a un petit hic, je ne souhaite pas convertir le tableau en objet tableau.

Du coup j'ai essayé de modifier ton code VBA mais.... étant pas très bon... ça ne marche

J'ai mis en couleur ce que j'ai tenté de faire.

Sub insérer_ligne()

Dim cell As Range, cell1 As Range

Dim i As Integer

With Feuil1.ListObjects(1)

' recherche dans tableau de suivi de la dernière cellule correspondant à la catégorie produit sélectionnée

Set cell = .ListColumns("Catégorie de produit").Range.Find(Range("B10:B60"), SearchDirection:=xlPrevious)

If Not cell Is Nothing Then

'recherche dans tableau de suivi de la dernière ligne correspondant à la catégorie produit et type produit sélectionné

Set cell1 = cell

Do

i = cell.Row - .HeaderRowRange.Row 'numéro de ligne du tableau

If .ListColumns("type de produit").DataBodyRange.Rows(i) = Range("C10:C60") Then Exit Do

Set cell = .ListColumns("Catégorie de produit").Range.FindPrevious(cell)

Loop Until cell.Address = cell1.Address

'ajout nouvelle ligne tableau derrière la dernière trouvée

i = i + 1

.ListRows.Add i

.ListColumns("Catégorie de produit").DataBodyRange.Rows(i) = Range("B10:B60")

.ListColumns("type de produit").DataBodyRange.Rows(i) = Range("C10:C60")

End If

End With

End Sub

Cordialement

Monsach

Le 18/09/2018 à 20:02

re

mettre un même fichier Excel entre 10 mains ?

mauvaise idée

pour plusieurs raisons, VBA en étant une.

il vaut mieux passer à Access ou autre SGBD

essaye au moins, tu verras la simplicité et la fiabilité et le partage des accès.

mébon, tu sembles déterminé, alors quel que soit ton choix, je te souhaite bon travail.

amitiés à toi et à tous

Le 18/09/2018 à 20:55

Bonsoir,

"cat_prod" est un nom qui fait référence à cellule B5 de la feuille: Suivi des stocks

"type_prod" est un nom qui fait référence à la cellule C5 de la feuille: Suivi des stocks

Si tu ne veux pas utiliser ces références de nom, alors il faut remplacer :

Range("cat_prod") par Range("'Suivi des stocks'!$B$5") ou ActiveSheet.Range("$B$5")

Range("type_prod") par Range("'Suivi des stocks'!$C$5") ou ActiveSheet.Range("$C$5")

ci-jointe version sans objet tableau

128tableau-de-suivi-des-stocks2.xlsm (30.54 Ko)

Le 18/09/2018 à 21:02

Bonjour

j'ais changer la macro de thev

peux plus simple pour toi

Sub inserer_ligne()
Dim Nlig&
Nlig = Range("B" & Rows.Count).End(xlUp).Row
    Range("B" & Nlig & ":T" & Nlig).ListObject.ListRows.Add , True
End Sub

A+

Maurice

Le 19/09/2018 à 19:21

thev a écrit :
Bonsoir,
"cat_prod" est un nom qui fait référence à cellule B5 de la feuille: Suivi des stocks
"type_prod" est un nom qui fait référence à la cellule C5 de la feuille: Suivi des stocks
Si tu ne veux pas utiliser ces références de nom, alors il faut remplacer :
Range("cat_prod") par Range("'Suivi des stocks'!$B$5") ou ActiveSheet.Range("$B$5")
Range("type_prod") par Range("'Suivi des stocks'!$C$5") ou ActiveSheet.Range("$C$5")
ci-jointe version sans objet tableau

Bonsoir thev,

Super, j'ai pu testé sur mon fichier au travail, ça fonctionne!!!

Je te remercie.

Cordialement

Monsach

Le 19/09/2018 à 19:24

archer a écrit :
Bonjour
j'ais changer la macro de thev
peux plus simple pour toi
Sub inserer_ligne()
Dim Nlig&
Nlig = Range("B" & Rows.Count).End(xlUp).Row
    Range("B" & Nlig & ":T" & Nlig).ListObject.ListRows.Add , True
End Sub
A+
Maurice

Bonsoir archer

J'ai testé ça marche bien aussi! Du coup maintenant j'ai l’embarras du choix.

Merci, c'est super sympa!

Cordialement

Monsach