Erreur aléatoire (mais fréquente) macro

Le 13/02/2018 à 17:20

Bonjour à Tous,

Je réalise ma première Macro VBA....et ça me rend fou

Je vous explique un peu le pb:

  • J'ai deux feuilles: feuille1 et feuille2
  • Lorsque un utilisateur sélectionne une cellule de la feuille 1 et clique sur le bouton, toute la ligne à laquelle la cellule sélectionnée appartient est copiée (exemple: l’utilisateur sélectionne C15, toute la ligne 15 est copiée)
  • Dans la feuille 2 une nouvelle ligne est crée à l'emplacement de la 3eme ligne, les enregistrements déjà présents sont décalés vers le bas.
  • Puis la ligne est collée à cet emplacement (donc dans notre exemple la ligne 15 de la feuille 1 est collée dans la ligne 3 de la feuille 2).
  • Deux cellules sont ensuite remplies: A3 avec la référence, B3 avec la date

Mon soucis est que ça fonctionne, mais que très partiellement, et je n'arrive pas à mettre le doigt sur ce qui fait planter la macro. Le message d'erreur est "

Erreur Automation

L'object invoqué s'est déconnecté de ses clients"

Parfois ça arrive la première fois que l'on l’exécute, parfois la seconde, parfois la 5eme ... c'est ce qui me rend perplexe.

Et voici mon code:

Sub copytest()

' Dans la feuille feuille1, copie la Ligne de la cellule selectionnée
   Sheets("feuille1").Select
   Rows(ActiveCell.Row).Select
   Selection.Copy

' Colle la ligne dans la feuille feuille2, en insérant une ligne
   Sheets("feuille2").Select
   ActiveSheet.Rows("3:3").Select
   Selection.Insert Shift:=xlDown

' Place la date
   Range("B3") = Format(Date, "mm-dd-yyyy")

' Comptage la ligne
   X = Application.WorksheetFunction.CountA(Range("C:C"))

' Construit la reference et la place dans la cellule A3
   Range("A3") = Format(Date, "yymm") & "-" & (X - 1)

'fin
 SendKeys "{ESC}"

End Sub

Quand je clique sur débogage, le curseur est sur Selection.Insert Shift:=xlDown

Voila, ce serait vraiment génial si quelqu'un pouvait m'aider. Merci

Le 13/02/2018 à 19:21

Bonjour juloni,

Je te propose ce fichier Excel :

16exo-juloni.xlsm (15.50 Ko)

En laissant en 1ère feuille la cellule active sur C15, regarde le contenu des 2 feuilles.

Ensuite, fais Ctrl e à partir d'une des 2 feuilles (au choix) ➯ travail effectué

Alt F11 pour voir la macro, puis revenir sur Excel

Je te laisse adapter ta variable X : ton commentaire est ' Comptage la ligne

mais ton instruction .CountA() est sur la colonne C !

Si besoin, tu peux demander une (autre) adaptation.

Merci de me dire si ça te convient.

Cordialement,

dhany

Le 13/02/2018 à 22:45

Bonsoir,

A essayer : 

Sub copytest()
    Dim n%, X&
    With Sheets("feuille1")
        n = .Cells(ActiveCell.Row, .Columns.Count).End(xlToLeft).Column
        .Range("A" & ActiveCell.Row).Resize(, n).Copy
    End With
    With Sheets("feuille2")
        .Range("A3").Resize(, n).Insert xlShiftDown
        With .Range("B3")
            .Value = Date: .NumberFormat = "mm-dd-yyyy"
        End With
        X = WorksheetFunction.CountA(.Columns("C"))
        .Range("A3") = Format(Date, "yymm") & "-" & (X - 1)
    End With
End Sub

Cordialement.

Le 14/02/2018 à 10:23

Merci énormément à vous deux, les deux solutions fonctionnent mais il reste un problème à l'utilisation:

Sur la feuille 1, il va y avoir environ 500 lignes. J'aimerais donc pouvoir filtrer ces colonnes pour que l'on trouve rapidement l'enregistrement que l'on cherche. Mais lorsque j'applique un filtre, que je sélectionne la cellule et que j’exécute la macro, j'ai une erreur

Erreur d'éxecution -2147417848

La méthode 'Insert' de l'object 'Range' a échoué.

Le débogage pointe sur la ligne

.Range("A3").Resize(, n).Insert xlShiftDown

(dans le code de MFerrand)

Y a t-il un moyen de faire fonctionner cette macro même quand un filtre est appliqué ?

Le 14/02/2018 à 10:55

Bonjour,

Ce type d'erreur est assez flou, on le voit souvent apparaître en relation avec un objet externe (erreur 'automation') ou alors dans des conditions particulières d'exécution (par exemple, feuille protégée)...

A-priori, en appliquant sur une ligne de plage filtrée, sélection cellule après filtrage, je ne l'ai pas ! Il faudrait donc voir ce qui la produit dans ta configuration particulière...

Si tu lances la macro à partir d'un bouton lorsque l'erreur se produit, essaie de la lancer dans les même conditions à partir de la boîte de dialogue macro, pour voir si elle se produit également dans ce cas ou non.

Cordialement.

Le 14/02/2018 à 11:19

En effet, ça reste aléatoire, même avec les filtres.

Par contre avec la recherche je n'ai pas d'erreur apparemment

Je vais continuer à tester la chose, et essayer de trouver une méthode qui ne plante jamais.

En tout cas un grand merci pour votre aide

Le 14/02/2018 à 11:35

Ah et tant que j'y suis, une autre question. Après promis je ne vous embête plus

Toujours dans la même macro, j'aimerais que dans la feuille 1, la date du jour soit mise dans la colonne L.

Exemple:

L'utilisateur sélectionne la cellule D20 et éxecute la marco.

Toute la ligne 20 se retrouve copiée vers la feuille 2.

Il faudrait que dans la feuille 1, la date du jour soit mise dans la cellule L20.

Est-ce faisable ?

Le 14/02/2018 à 11:41

Si "Feuil1" est la feuille active :

Cells(ActiveCell.Row, "L") = Date

Si "Feuil1" n'est pas la feuille active, tu as aussi :

Worksheets("Feuil1").Cells(20, "L") = Date

⚠ ActiveCell.Row marche seulement sur la feuille active,

donc à toi de trouver une solution !

dhany

Le 14/02/2018 à 11:56

Merci danhy ça marche nickel

Le 14/02/2018 à 11:58
Sub copytest()
    Dim n%, i&, X&
    With Sheets("feuille1")
        i = ActiveCell.Row
        n = .Cells(i, .Columns.Count).End(xlToLeft).Column
        .Range("A" & i).Resize(, n).Copy
    End With
    With Sheets("feuille2")
        .Range("A3").Resize(, n).Insert xlShiftDown
        With .Range("B3")
            .Value = Date: .NumberFormat = "mm-dd-yyyy"
        End With
        X = WorksheetFunction.CountA(.Columns("C"))
        .Range("A3") = Format(Date, "yymm") & "-" & (X - 1)
    End With
    With Sheets("feuille1").Range("L" & i)
        .Value = Date: .NumberFormat = "mm-dd-yyyy"
    End With
End Sub

Ainsi ?

Le 14/02/2018 à 12:03

Je l'ai placé là et ça fonctionne:

Sub copytest()

Dim n%, X&

With Sheets("feuille1")

Cells(ActiveCell.Row, "L") = Date

n = .Cells(ActiveCell.Row, .Columns.Count).End(xlToLeft).Column

.Range("A" & ActiveCell.Row).Resize(, n).Copy

End With

With Sheets("feuille2")

.Range("A3").Resize(, n).Insert xlShiftDown

With .Range("B3")

.Value = Date: .NumberFormat = "mm-dd-yyyy"

End With

X = WorksheetFunction.CountA(.Columns("C"))

.Range("A3") = Format(Date, "yymm") & "-" & (X - 1)

End With

End Sub

Le 14/02/2018 à 12:12

Oui, c'est parfait : tu as placé l'instruction au bon endroit ! et ça marche si tu lances ta macro copytest() à partir de "feuille1" ; mais attention : l'instruction With Sheets("feuille1") est juste une référence à la feuille "feuille1", ça ne la sélectionne pas. donc si tu lances copytest() à partir de "feuille2", tu risques de ne pas obtenir le résultat souhaité : à vérifier !

Le 14/02/2018 à 12:22

Il faudra que l'utilisateur lance la macro depuis la feuille 1

donc a la limite ça m'arrange que cela ne fonctionne pas depuis la feuille2...

Merci pour votre aide à tous les deux

Le 14/02/2018 à 12:33

Re,

Je conseillerais :

1) de conserver les qualificateurs d'objet des expressions, soit de ne pas omettre un point devant .Cells qui fait référer l'expression explicitement à Sheets("feuille1").

C'est une garantie à la fois de fiabilité et de rapidité du code...

2) de respecter la syntaxe officielle de Cells, soit : Cells(numéro de ligne, numéro de colonne) en évitant de substituer la lettre de colonne à son numéro... même si cela fonctionne ! Cela évitera de se poser des questions le jour où cela ne fonctionne pas !!!

Remarque :

Placée à cet endroit, cette commande aboutit à ce que la date mise en L se retrouvera feuille2 (copiée). La cellule n'étant pas formatée identiquement à la cellule B3 de feuille2, elle apparaîtra donc sous le format par défaut : jj mm aaaa.

Remarque2 :

Il est bon de citer le code dans un post en le mettant sous balises Code. Cela fait en sorte qu'il soit plus lisible... et c'est donc bien mieux pour ceux qui le lisent ! Cela en conserve aussi l'indentation lorsqu'il est indenté (et mon code est toujours systématiquement indenté ! ), ce qui est également un élément essentiel de lisibilité, qui facilite la détection des erreurs, ainsi que tout autre travail sur le code qui pourra se réaliser toujours plus rapidement que s'il ne l'était pas...

Cordialement.

Le 14/02/2018 à 13:26

@ MFerrand

Pour les conseils, est-ce que ce sont des choses que tu as remarquées dans mon code et qu'il faudrait que je change ? Ou juste des conseils généraux ?

Et pour les remarques:

Remarque1: Oui je comprends, la date par défaut me convient.

Remarque2: j'avais mis les balises code dans mes post précédents, mais désolé dans le dernier je voulais attirer l'attention sur une ligne en spécifique (en mettant la ligne en gras), du coup je n'ai pas mis les balises.

Encore merci

Le 14/02/2018 à 13:37

Les conseils sont liés à ta ligne de code en gras...

Par ailleurs il est évident que démarrant sur une sélection de cellule, il faut que cette sélection soit dans la feuille à traiter, ainsi que l'a souligné Dhany. Si tu lances la macro avec un bouton se trouvant sur ladite feuille, pas de problème : pour cliquer sur le bouton tu devras te trouver sur la feuille... Si tu la lances autrement, tu as intérêt à mettre une ligne vérifiant que si la feuille active n'est pas la bonne il ne faut pas poursuivre...

Cordialement.

Le 14/02/2018 à 14:26
Rechercher des sujets similaires à "erreur aleatoire frequente macro"