Macro insertion ligne copie formule

picoteuse · Décembre 26, 2009, 10:28

Bonsoir,

Je me permets de vous écrire car j’ai un problème avec la création d’une macro.

Je souhaite insérer dans ma feuillle excel nommée ‘OF’ un bouton macro afin d’insérer automatiquement une ligne… Cependant, cette ligne doit recopier automatiquement les formules de la ligne au-dessus, mais sans les données texte tapées à la main.

Voici les données qui seront introduites dans la feuille :

Colonne A: formule afin de numéroter la ligne (=A10+1), il y a peut-être un meilleur moyen de numéroter, mais je souhaite commencer la numérotation à 200, pour une recherche VLOOKUP par la suite. Attention, je souhaite cacher cette colonne car elle ne concerne pas les autres utilisateurs.

Colonne B -> D, I - O : texte

Colonne E - G - H … : liste déroulante via le menu Validation

Colonne Q - Z , … = formule un peu compliquée à expliquer, mais qui est indispensable (elle fait référence aux deux colonnes précédentes)

En tout, les colonnes du classeur vont de A à AJ. Chacune de ces colonnes comporte ces trois types de données.

Le but est donc de créer un bouton qui insérerait une nouvelle ligne (ou même plusieurs à la fois, à choisir par l’utilisateur, mais en respectant la numérotation en A), en recopiant les formules et listes déroulantes, mais sans texte dans les cases textes. Eventuellement on pourrait également trouver une meilleure façon de numéroter les nouvelles lignes, mais tout on laissant la possibilité de commencer la numération à un nombre choisi.

Pour l’instant, j’ai colorié une cellule en jaune, en demandant à excel de trouver cette cellule et de copier toute la ligne précédente, et ensuite de la vider par une solution certainement pas catholique pour vous. Les 3 codes fonctionnent tout à fait bien de manière indépendante, mais mise bout à bout j’ai un code erreur. A mon avis car la première fonction de recherche ne sélectionne pas ma case jaune.

Ci-dessous, je vous fais une copie du code :

Private Sub CommandButton1_Click()

Recherche de la case jaune unique dans ma feuille ‘OF’

Dim Rg As Range
Dim LeCellFormat As CellFormat

Set LeCellFormat = Application.FindFormat

With LeCellFormat.Clear.Interior.ColorIndex = 36
End With

With Worksheets(“OF”)
Set Rg = .Range(“B:B”)
End With

With Rg
Set C = .Find(What:="", SearchFormat:=True)

If Not C Is Nothing Then
C.Select
End If

Sélection et copier-coller de la ligne précédente

EntireRow.Select

Rows(Cell.Row - 1).Copy
Rows(Cell.Row).Insert shiftXldown
Application.CutCopyMode = False

Effacement du contenu des cellules ‘Texte’ dans les autres colonnes et retour à la colonne B. A certains endroits, je lui dis de passer deux lignes, car il y a une formule qui doit rester.

ActiveCell.Offset(0, 0).Select
Selection.ClearContents

ActiveCell.Offset(0, 1).Select
Selection.ClearContents

ActiveCell.Offset(0, 2).Select
Selection.ClearContents

ActiveCell.Offset(0, 1).Select
Selection.ClearContents

ActiveCell.Offset(0, 2).Select
Selection.ClearContents

ActiveCell.Offset(0, 1).Select
Selection.ClearContents

ActiveCell.Offset(0, 2).Select
Selection.ClearContents

ActiveCell.Offset(0, 1).Select
Selection.ClearContents

ActiveCell.Offset(0, -35).Select

End With

End Sub

Je suis vraiment désolée pour ces codes qui vous sembleront certainement très moches, mais j’ai tenté pendant de longues heures de me débrouiller toute seule, sans succès cependant.

Un grand merci d’avance pour votre aide bien précieuse.

gcc · Décembre 28, 2009, 1:26

Essaie la méthode suivante :
1- tu te positionnes sur la ligne à recopier (je n’ai pas très bien compris ton code pour la recherche de la formule en question ; que se passe-t-il s’il y a plusieurs cellules avec des fonds colorés ?)

2- tu insères une ligne recopiée en dessous de ta sélection
Selection.EntireRow.Copy
Selection.Insert shiftXldown

3- dans cette nouvelle ligne, et en te limitant aux colonnes utilisées, tu effaces tous les contenus qui ne sont pas des formules, autrement dit tous les contenus qui ne commencent pas par “=”
For Each c In Intersect(ActiveSheet.UsedRange, Selection.Offset(1).EntireRow)
If Left(c.Formula, 1) <> “=” Then c.Value = “”
Next

picoteuse · Décembre 28, 2009, 9:15

Bonsoir gcc,

Un grand merci pour ta réponse.

Ton point 3 correspond parfaitement à mes attentes, la formule est bien simplifiée.

Par contre, j’ai essayé les codes 1 & 2 qui fonctionnent (en partie seulement), mais j’aurais souhaiter que la macro détecte automatiquement la première ligne vide ou la cellule jaune. Il n’y aura d’office qu’une cellule jaune car je l’ai rajoutée expressément (en pensant que cela serait plus simple pour détecter la première ligne vide).

Pour t’expliquer, j’ai créer ce fichier (nommé Suivi) pour des collègues dont beaucoup ne connaissent rien en Excel. Rajouter une ligne n’est pas compliqué c’est vrai, mais il est très important qu’ils remplissent les données à la suite des autres (une numérotation des offres est prévue en B) et de plus j’ai une autre numérotation dans la colonne A qui me permet de faire apparaître dans une autre fichier (Rapport Sales) des données grâce à une recherche VLOOKUP. La numération en colonne A sera différente de la colonne B, car dans mon autre fichier qui compile les données (Rapport Sales) je vais chercher les données dans 4 fichiers (Suivi) différents.

Pour l’instant, faute de mieux, dans la colonne A j’ai mis une formule (=A12+1). Il est donc très important que la macro copie la dernière ligne remplie, et la colle juste après.

J’espère que mes explications ont été plus claires.

Un grand merci d’avance pour ton aide.

Ps : le code actuel (1 & 2) ne fonctionne pas tout à fait correctement, car la numération en colonne A déconne)

gcc · Décembre 29, 2009, 11:03

Si j’ai bien compris, tu cherches à insérer une ligne juste avant la première cellule vide rencontrée (dans le sens vertical) depuis le haut du tableau.
Dans ce cas, et si le tableau a au moins déjà deux lignes (ex ligne de titres et première ligne de données), ça devient assez simple

un exemple en supposant que ta ligne de titres soit en ligne 2


    Range("A2").Select  'la première cellule de la ligne de titre 
    Selection.End(xlDown).Select  'la dernière cellule de la colonne A avant la cellule vide
    Selection.EntireRow.Copy
    Selection.Insert shiftXldown
    Selection.Offset(1).Select    'positionnement sur la nouvelle ligne
    For Each c In Intersect(ActiveSheet.UsedRange, Selection.EntireRow)
        If Left(c.Formula, 1) <> "=" Then c.Value = ""
    Next
    Selection = Selection.Offset(-1) + 1    ' incrémentation de la valeur en colonne A

la dernière ligne de code devrait normalement résoudre ton problème de valeur en colonne A :
plutôt qu’une formule excel, on utilise la macro pour déduire de la ligne précédente la valeur à inscrire
Tu as donc dans ce cas juste à mettre la valeur de ton choix dans la première ligne, et non plus une formule

picoteuse · Décembre 29, 2009, 1:32

Bonjour gcc,

Un grand merci pour ton aide… Le code marche parfaitement.

Puis-je encore abuser de ton aide pour deux petites finitions ?

1/ La nouvelle ligne ‘clignote’ comme si on l’avait sélectionnée et copiée. Y-a-t-il moyen d’enlever cela pour ne pas devoir faire Escape à chaque fois.

2/ Serait-il possible de positionner mon curseur sur la cellule B de la nouvelle ligne insérée ?

Si ces deux problèmes sont résolus, je ne t’embête plus.

Merci beaucoup pour ton aide.

gcc · Décembre 29, 2009, 5:50

il faut que tu remettes la ligne
Application.CutCopyMode = False
comme tu l’avais fait la première fois pour annuler la sélection après l’insertion

Il suffit de rajouter une ligne à la fin du code pour déplacer la sélection d’une cellule vers la droite :
Selection.Offset(0, 1).Select

picoteuse · Décembre 29, 2009, 7:08

Bonsoir Gcc,

Un grand merci pour tes derniers conseils.

Le fichier est parfait maintenant.

Merci pour ta patience.

gcc · Décembre 30, 2009, 9:47

:jap: