Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Macro qui reboucle. sans boucle [XL-2016]


Sujet :

Macros et VBA Excel

  1. #1
    Futur Membre du Club
    Homme Profil pro
    Responsable des études
    Inscrit en
    octobre 2012
    Messages
    12
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : octobre 2012
    Messages : 12
    Points : 9
    Points
    9
    Par défaut Macro qui reboucle. sans boucle
    Bonjour,

    En fait je voudrais remplacer des formules excel par des macros afin de sécuriser le fonctionnement de mon fichier.

    Voila, disons que j'ai deux colonnes.
    - celle de gauche (B3:B20) contient des références
    - celle de droite (C3:C20), un texte qui se met à jours suite à l'entrée de la référence correspondante dans la cellule de gauche (même ligne évidement)

    Au début je voulais vous demander comment faire, mais en programmant une petit exemple j'ai finalement trouvé la solution.

    J'ai cependant un petit probléme...

    après exécution, ma macro reboucle et plante.
    j'ai mise des étapes d'arrêt, tout fonctionne sans problème au moins jusque la ligne 13 puisque le résultat final est bon.
    Par contre la petite flèche jaune revient ensuite sur la ligne 8 et là :

    "Erreur d'exécution '62147417848 (80010108)'
    La méthode 'Find' de l'objet ' Range' a échoué
    Si vous pouviez me guider ça ne serait pas de refus

    idem, pensez vous que la méthode soit la bonne, pour ma part j'ai l'impression de ne pas pouvoir faire mieux.

    bien à vous

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
     
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(ActiveCell, Range("B3:B20")) Is Nothing Then
     
    Ref_Active = ActiveCell.Offset(-1, 0).Value
     
        If Ref_Active <> "" Then
        Set Reference = Sheets("calc prix vente").Range("B:B").Find(Ref_Active, , xlValues, xlWhole, , , False)
     
            If Not Reference Is Nothing Then
            Designation = Reference.Offset(0, 1).Value
     
            ActiveCell.Offset(-1, 1).Value = Designation
     
            End If
        End If
    End If
    End Sub

  2. #2
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 209
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 209
    Points : 3 689
    Points
    3 689
    Par défaut
    Bonjour,

    bien que le message d'erreur m'étonne, ton pb vient peut-etre que tu ne désactives pas les événements alors que tu écris et déclenche à nouveau l'événement Change.
    Bon, tu écris dans une autre colonne que celle supervisée, tu n'y retournes à tort qu'une seule fois.

    D'autres remarques.
    Tu reçois Target en paramètre, plage des cellules modifiées.
    Il faut t'en servir et non pas d'ActiveCell.
    Comme tu peux avoir plusieurs cellules modifiées lors d'un collage il faut en tenir compte.
    Soit sortir de la Sub si Target.Count > 1, soit boucler et traiter toutes les cellules intersection du Target avec la plage supervisée.
    C'est à dire :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim pl As Range, c As Range
        Set pl = Intersect(Target, Range("B3:B20"))
        If Not Intersect(Target, pl) Is Nothing Then
            For Each c In pl
                Ref_Active = Target.Offset(-1, 0).Value
                If Ref_Active <> "" Then
                    Set Reference = Sheets("calc prix vente").Range("B:B").Find(Ref_Active, , xlValues, xlWhole, , , False)
                    If Not Reference Is Nothing Then
                        Designation = Reference.Offset(0, 1).Value
                        Application.EnableEvents = False
                        Target.Offset(-1, 1).Value = Designation
                        Application.EnableEvents = True
                    End If
                End If
            Next c
        End If
    End Sub
    Non testé bien sur.
    eric

  3. #3
    Futur Membre du Club
    Homme Profil pro
    Responsable des études
    Inscrit en
    octobre 2012
    Messages
    12
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : octobre 2012
    Messages : 12
    Points : 9
    Points
    9
    Par défaut
    Salut Eriiic

    Merci pour ta solution,

    En fait utiliser target permet visiblement de ne plus utiliser "offset (-1" vu que avec activcell la cellule active après avoir pressé entré est la cellule du dessous ;-)
    idem ça règle le problème de mon message de défaut.

    Par contre bizarrement la désignation complété est décalé d'une référence... (première désignation vide, la suivante correspond à celle de la référence d'avant) Va savoir pourquoi ;-).

    Mais avec ta macro, là c'est bon partout et du premier coup...

    Ce qui m'énerve dans l'histoire c'est que je ne vois pas le lien avec la désactivation des événements mais qu'il semblerai que la solution soit là....

    Par contre du coup....
    Pourquoi utilise tu une boucle?
    tu met à jour toutes les lignes à chaque fois?

    il est probable que j'ajuste la désignation après collage, du coup dans ce cas il faudra que j'ajoute une ligne pour ne pas recoller si la cellule est pleine ,-)

    Merci à toi en tout cas.

  4. #4
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 209
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 209
    Points : 3 689
    Points
    3 689
    Par défaut
    Bonjour,

    Pourquoi utilise tu une boucle?
    tu met à jour toutes les lignes à chaque fois?
    Non, tu n'as pas lu les explications ?
    Comme expliqué, un collage peut modifier plusieurs lignes d'un coup.
    Je me répète mais soit tu arrêtes le traitement et ne fait rien, soit tu traites toutes les lignes concernées.
    Ignorer cette éventualité comme tu faisais fait que tu ne traites que la 1ère ligne. Si on ne regarde que celle-ci on peut penser que tout le traitement a été fait.

    Si une seule ligne est modifiée, la boucle n'est parcourue qu'une fois.

    Par contre bizarrement la désignation complété est décalé d'une référence... (première désignation vide, la suivante correspond à celle de la référence d'avant) Va savoir pourquoi ;-).
    Sans fichier ni détail de ce que tu veux, impossible de tester.
    J'ai laissé des partie de ton code à l'identique. Il reste sans doute des offset ligne que tu avais ajoutés pour compenser l'utilisation d'activecell qui deviennent inutiles.
    C'est toi qui connait la logique de ton code et ton besoin qui peux trouver ce qu'il faut encore adapter.
    Tu as le pas à pas et l'espionnage des variables pour déboguer ça. On ne peut pas programmer sans connaitre les outils de débogage de base.
    eric

  5. #5
    Futur Membre du Club
    Homme Profil pro
    Responsable des études
    Inscrit en
    octobre 2012
    Messages
    12
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : octobre 2012
    Messages : 12
    Points : 9
    Points
    9
    Par défaut
    Salut Eric

    En fait au début j'avais juste remplacé ActiveCell par target pour voir l'évolution puis modifié les offsets au vu de l'évolution du programme.
    c'est ensuite que j'ai testé ta macro qui roule quasiment sans problème.

    bien vu pour le collage. c'est vrais que c'est mieux de le prendre en compte dés le début ;-).

    J'ai apporté une petite correction concernant la gestion des cellules vide (si j’efface tout).

    Par contre il reste un problème si je sort de la plage B21, j'ai un "Argument ou appel de procédure incorrect"

    Merci à toi pour ton aide ;-)


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim pl As Range, c As Range
     
        Set pl = Intersect(Target, Range("B3:B20"))
     
        If Not Intersect(Target, pl) Is Nothing Then
     
            For Each c In pl
                Ref_Active = Target.Offset(0, 0).Value
     
                If IsEmpty(Ref_Active) = False Then
                    Set Reference = Sheets("calc prix vente").Range("B:B").Find(Ref_Active, , xlValues, xlWhole, , , False)
     
                    If Not Reference Is Nothing Then
                        Designation = Reference.Offset(0, 1).Value
                        Nb = Reference.Offset(0, 2).Value
                        Unite = Reference.Offset(0, 3).Value
     
                        Application.EnableEvents = False
                        Target.Offset(0, 1).Value = Designation
                        Target.Offset(0, 2).Value = Nb
                        Target.Offset(0, 3).Value = Unite
                        Application.EnableEvents = True
                    End If
     
                End If
            Next c
        End If
    End Sub

  6. #6
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 209
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 209
    Points : 3 689
    Points
    3 689
    Par défaut
    si je sort de la plage B21
    Ça veut dire quoi concrètement ?

    j'ai un "Argument ou appel de procédure incorrect"
    Où ça ? Sur quelle ligne de programme ?
    J'ai du mal à imaginer que ce soit dans la partie que j'ai faite, car si aucune cellule de B3:B20 n'est modifiée on sort directement de la procédure.
    Tout cela est bien flou...
    eric

    PS : Target.Offset(0, 0).Value c'est Target.Value.
    Il faut quand même prendre le temps d'essayer de comprendre ce que tu fais sinon tu ne progresseras jamais.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
               Ref_Active = Target.Offset(0, 0).Value
                If IsEmpty(Ref_Active) = False
    IsEmpty est une variable non initialisée, chose que tu fais juste au-dessus.
    Tu n'es pas près d'avoir ce test négatif...
    Ne pas confondre IsEmpty(Ref_Active) et Ref_Active="".
    D'ailleurs il faut typer toutes tes variables, par défaut elles sont Variant. Là on ne sait pas si Ref_Active est une chaîne ou un nombre.

  7. #7
    Membre à l'essai
    Homme Profil pro
    automatitien
    Inscrit en
    novembre 2015
    Messages
    25
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : automatitien
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : novembre 2015
    Messages : 25
    Points : 12
    Points
    12
    Par défaut
    heu tu as l'impression que je n'essai pas de comprendre...?
    désolé pour ça....

    J'ai bien compris le principe de l'offset excuse moi, j'aurai effectivement du raccourcir la ligne.
    c'est une erreur d’inattention de fin de journée.

    B21 : je n'ai pas été très claire, ce que je voulais dire c’est que j'ai une erreur si je sort de la plage B3:B20,donc en B21 lors de mon test (mais Z56 c'est pareil).

    j'ai un "Argument ou appel de procédure incorrect"
    Où ça ? Sur quelle ligne de programme ?
    > Ligne 10

    Je te l'accord malgré ce que j'avais cru voir : "If IsEmpty(Ref_Active) = False Then" ne résous pas mon problème.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    Option Explicit
     
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim pl As Range, c As Range, Reference As Range
        Dim Ref_Active As String, Designation As String, Nb As String, Unite As String
     
       'On défini la plage de travail
        Set pl = Intersect(Target, Range("B3:B20"))
     
       'On vérifie que si la plage sélectionnée (Target) se trouve dans la plage "pl"
        If Not Intersect(Target, pl) Is Nothing Then
     
        'On boucle sur les cellules modifiées
            For Each c In pl
                Ref_Active = Target.Value
     
                'Si la valeur en cours n'est pas vide, on éxécute la suite.
                If IsEmpty(Ref_Active) = False Then
                 'If Ref_Active <> "" Then
                    Set Reference = Sheets("calc prix vente").Range("B:B").Find(Ref_Active, , xlValues, xlWhole, , , False)
     
        'On récupére les caractéristiques du produit
                    If Not Reference Is Nothing Then
                        Designation = Reference.Offset(0, 1).Value
                        Nb = Reference.Offset(0, 2).Value
                        Unite = Reference.Offset(0, 3).Value
     
        'On Colle les Caractéristiques du produit sur la ligne
                        Application.EnableEvents = False
                        Target.Offset(0, 1).Value = Designation
                        Target.Offset(0, 2).Value = Nb
                        Target.Offset(0, 3).Value = Unite
                        Application.EnableEvents = True
                    End If
     
                End If
            Next c
        End If
    End Sub
    Bonne Nuit!!!

  8. #8
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 209
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 209
    Points : 3 689
    Points
    3 689
    Par défaut
    Bonjour,

    ah oui. J'avais modifié en dernière minute en extrayant avec le set pl = au-dessus, et oublié de modifier la suite.
    Remplace par :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    If Not pl Is Nothing Then
    eric

  9. #9
    Futur Membre du Club
    Homme Profil pro
    Responsable des études
    Inscrit en
    octobre 2012
    Messages
    12
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : octobre 2012
    Messages : 12
    Points : 9
    Points
    9
    Par défaut
    Salut Eriic,

    Désolé j'étais en déplacement

    Je comprend un peu mieux la fonction intersect

    Par contre j'ai encore un bug.... lorsque j'efface mon tableau la macro s’exécute et plante.

    j'ai essayé de modifier le code pour suivre les réactions mais ça ne change rien et pour le coup je n'y comprend rien du tout....

    Merci d'avance pour ton aide.

    Valery

  10. #10
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 209
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 209
    Points : 3 689
    Points
    3 689
    Par défaut
    Bonjour,
    "ça plante" ne veut rien dire...
    La ligne en cause et le message d'erreur est quand même un minimum.
    Pas sûr à 100% que ce soit la cause mais je t'invite à relire la fin de mon post #6.
    eric

  11. #11
    Futur Membre du Club
    Homme Profil pro
    Responsable des études
    Inscrit en
    octobre 2012
    Messages
    12
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : octobre 2012
    Messages : 12
    Points : 9
    Points
    9
    Par défaut
    Hello,

    Tu parle du typage des variables?

    j'ai ajouté Option Explicit en début de programme, vu que c'est un travail que je n'aime pas faire j'ai été bien obligé de m'y coller du coup ;-)

    Alors pour être plus précis, il semblerai que je n'ai le défaut que lorsque je supprime plusieurs cellule en même temps
    alors qu'avec une seul pas de soucie...

    la scrutation s’arrête à la ligne 19 ci dessous

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
      Option Explicit
     
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim pl As Range, c As Range, Reference As Range
        Dim Ref_Active As String, Designation As String, Nb As String, Unite As String
     
       'On défini la plage de travail
        Set pl = Intersect(Target, Range("A:A"))
     
        'On vérifie que si la plage est Vide
        If Not pl Is Nothing Then
     
        'On vérifie que si la plage sélectionnée (Target) se trouve dans la plage "pl"
        If Not Intersect(Target, Range("A:A")) Is Nothing Then
     
        'On boucle sur les cellules modifiés
            For Each c In pl
     
            If Target.Value <> "" Then
                Ref_Active = Target.Value
     
                'Si la valeur en cours n'est pas vide, on éxécute la suite.
                 If Ref_Active <> "" Then
                    Set Reference = Sheets("calc prix vente").Range("A:A").Find(Ref_Active, , xlValues, xlWhole, , , False)
     
                    If Target.Offset(0, 1) = "" Then
     
        'On récupére les caractéristiques du produit
                    If Not Reference = "" Then
                        Designation = Reference.Offset(0, 1).Value
                        Unite = Reference.Offset(0, 2).Value
     
        'On Colle les Caractéristiques du produit sur la ligne
                        Application.EnableEvents = False
                        Target.Offset(0, 2).Value = Designation
                        Target.Offset(0, 3).Value = Unite
                        Application.EnableEvents = True
     
                    End If
                    End If
     
                End If
                End If
            Next c
            End If
        End If
    End Sub
    avec le message :
    Erreur d'exécution '13':
    Incompatibilité de type
    Bien à toi.

    Valery

  12. #12
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 209
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 209
    Points : 3 689
    Points
    3 689
    Par défaut
    Bonjour,

    tu n'aurais pas une valeur d'erreur dans cette cellule des fois ?
    Si tu peux en avoir il faut tester avant :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    If IsError(ta_cellule) then
     
    else
     
    endif
    Et pourquoi tu continues avec Ref_Active ?
    D'autant plus qu'avec un nom comme ça on pense à une adresse et tu y mets le .Value (?!?)
    Utilise Target suivi de la propriété qui t'intéresse : .Value, .Address, etc
    eric

  13. #13
    Futur Membre du Club
    Homme Profil pro
    Responsable des études
    Inscrit en
    octobre 2012
    Messages
    12
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : octobre 2012
    Messages : 12
    Points : 9
    Points
    9
    Par défaut
    Oui d'autant plus que j'ai écrit deux fois la même chose

    Non pas de valeur d'erreur (enfin je pense) vu que j'efface le contenu du groupe de cellule.

    en fait si j'efface A1 par de soucie.
    Si j'efface A2, Non plus.
    Par contre si j'effece A1 et A2 en même temps ça coince....

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Boucle macro qui s'arrête après plusieurs tours sans arriver à la fin
    Par plamouik dans le forum Macros et VBA Excel
    Réponses: 11
    Dernier message: 14/09/2016, 10h04
  2. [XL-2007] Macro qui tourne en boucle, boucle FOR
    Par kilitiger dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 17/03/2016, 16h12
  3. [XL-2013] Problème macro qui BOUCLE sans raison
    Par tyndare36 dans le forum Macros et VBA Excel
    Réponses: 3
    Dernier message: 10/11/2014, 14h36
  4. Macro qui se lance toute seule, sans mon autorisation
    Par csempere dans le forum VBA Word
    Réponses: 5
    Dernier message: 06/06/2009, 09h10
  5. [E-03] Macro qui se lance tous les jours sans ouvrir le document excel ?
    Par doublenico dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 30/10/2008, 10h06

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo