Discussion :

[valerypetit]

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

[eriiic]

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

[valerypetit]

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.

[eriiic]

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

[valerypetit]

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

[eriiic]

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.

[valery.petit]

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!!!

[eriiic]

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

[valerypetit]

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

[eriiic]

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

[valerypetit]

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

[eriiic]

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

[valerypetit]

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....