Discussion :

[Hindioumax]

Bonjour à tous et toutes,


J'ai besoin de vos lumières car après un collage de donnée, ma fonction vlookup (recherchev) incluse dans l'événement Worksheet_Change(ByVal Target As Range) génère une erreur.

Finalité :
Coller des références d'articles figurant dans un classeur lamba dans mon classeur [commande.xls] et qu'après le collage (spécial ou pas), la désignation de ces références s'affiche automatiquement dans la colonne à coté.
Et cela sans aucune formule dans les cellules du classeur. La recherche vlookup étant placée dans un événement Worksheet_Change qui par définition s'exécute quand des cellules sont modifiées. Pour info, le tableau avec le couple ref/designation se situe dans l'onglet "base article".[/FONT]

Le problème:
Après un collage, mon événement Worksheet_SelectionChange s'execute bien mais ma vlookup me retourne le message d'erreur 1004.
La recherche fonctionne pourtant comme il faut quand je rentre une référence manuellement.


Mon code VBA dans la feuille "commande"

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
48
49
Private Sub Worksheet_Change(ByVal Target As Range)
 
  ' Protection des erreurs
    On Error GoTo err:
 
  ' Eviter que l'événement se rappelle à lui-meme lorsqu'il ajoutera les
  ' données dans la colonne B
    Application.EnableEvents = False
 
  ' Declaration des variables
    Dim sh_art As Worksheet
    Dim plage As String
 
  ' Affectation,
  ' Ma feuille ou se situe le tableau référence/désignation des articles
    Set sh_art = Worksheets("base_article")
 
  ' Plage de référence où se trouve mes données pour ma Recherchev (vlookup)
    plage = "A2:B100"
 
  ' Avant de faire ma recherchev, je m'assure :
  '    - d'être situé dans la colonne A (target.column = 1)
  '    - d'être au moins sur la ligne 2 (target.row > 1)
  '    - ma sélection ne dépasse pas 2 colonnes sinon N/A dans les colonnes suivantes (Target.Columns.Count = 1)
  '    - et que target.value ne soit pas vide Target.Value <> ""
  Debug.Print ("Valeur actuelle:" & Target.Value)
  Debug.Print (Target.Column)
  Debug.Print (Target.Row)
  Debug.Print (Target.Columns.Count)
 
    If Target.Value <> "" And Target.Column = 1 And Target.Row > 1 And Target.Columns.Count = 1 Then
     ' le résultat de la recherchev s'affiche dans la colonne B (Target.Offset(0, 1).Value)
       Target.Offset(0, 1).Value = ""
       Target.Offset(0, 1).Value = WorksheetFunction.VLookup(Target.Value, sh_art.Range(plage), 2, 0)
       Target.Offset(0, 1).Font.ColorIndex = 1
 
       Application.EnableEvents = True
    End If
 
    Exit Sub
' Si aucune reférence n'est trouvée,
' interception de l'erreur 1004 retournée par Vlookup
err:
If Target.Column = 1 And Target.Row > 1 And Target.Columns.Count = 1 Then
   Target.Offset(0, 1).Value = CvErr(xlErrNa)
   Target.Offset(0, 1).Font.ColorIndex = 3
   Application.EnableEvents = True
End If
End Sub

J'avoue que je sèche un peu et j'ai passé trop de temps dessus pour avoir le recul nécessaire sur la solution.
Si quelqu'un pouvait m'éclairer

Edit: j'ai modifié le script concernant la génération d'un vrai message d'erreur.

Le fichier "commande.xls"

[tirex28]

Bonsoir,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Application.EnableEvents = True

Il n'y a plus de probleme si tu sort ces instructions des blocs If .. End If

Au passage tu peux recuperer une vrai valeur d'erreur plutot qu'une simple valeur texte avec:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Target.Offset(0, 1).Value = CvErr(xlErrNa)

Cordialement,

Tirex28/

[Hindioumax]

Sans les Application.EnableEvents = True le script se relance à chaque qu'il inscrit soit une erreur soit une donnée qui figure dans mon tableau de la feuille "base_article". Leur présence est donc justifiée.

De plus si je les supprime, mon événement est désactivé et par conséquent ne fonctionne plus.

Quoi qu'il en soit, avec ou sans, ma fonction principale de recherche de valeur ne fonctionne pas quand je fais un "coller".


Tu as téléchargé mon classeur et que ca marche, Je désespère !

[alsimbad]

salut
vite lu, j'espere pas dire de betise
quand le code colle une donnée tu lance le "quand change"
et quand change tu calcule le vlookup et donc tu rechange et donc tu relance le quand change; et bref, il s'en sort pas
moi perso, je met une variable booleenne. a la premiere fois ça passe a true, et a la deuxieme, si true exit sub.
pas très formel comme solution, mais bon, ça peut le faire

[Hindioumax]

En français, mon script devrait faire :

A chaque fois que tu change une cellule dans la colonne A, tu me cherche la correspondance en faisant la RechercheV. Tu me met le résultat dans la colonne B (erreur ou pas).
Le script marche très bien quand je rentre une référence à la main.

Par contre il ne fonctionne pas quand je fais un "coller" de plusieurs référence d'un coup !

Le Application.EnableEvents est un petit plus que j'ai rajouté pour justement éviter que l'événement s'appelle lui même quand il écrit soit une erreur soit un bon résultat.

Le problème n'est pas là ! help !!

[alsimbad]

ben ma foi, sans vouloir etre plus royaliste que le roi :
je ne peux pas refaire ton probleme sur ma bécane, donc ce qui suit n'est que supposition et non afirmation
tu ne dis pas si tu a essayé ce qui allait dans le sens de ce que je te dis, tu dis juste que ça ne peut pas etre. question, je lance une autre idée ou pas?
bref, passons
si je résume quand tu dis je colle plusieurs données
je traduis
evenement , je colle une donnée
résultat : le code calcul
donc pour trois données
je colle n° 1 quandchange calcule et modifie celulle cible
modif cible = quand change calcul
quand change s'execute
je colle 2 et quandchange calcule et modifie celulle cible
modif cible = quand change calcul le evnts fonctionne mais peut etre qu'il a des limites.
moi je ne t'ai pas proposé une explication mais un embryon de solution. un minimum de politesse consisterai a dire
M.... mais ça colle pas tout a fait a ce que je recherche, ce serais mieux si.. ou j'ai essayé et ça colle pas parce que ..

[tirex28]

Sans les Application.EnableEvents = True le script se relance à chaque qu'il inscrit soit une erreur soit une donnée qui figure dans mon tableau de la feuille "base_article". Leur présence est donc justifiée.

Je ne dis pas le contraire mais simplement qu'ils sont mal placés. Tu mets False à chaque fois que l'évenement est déclenché donc tu doit remettre la valeur à True quoiqu'il arrive avant la fin de la procédure.

De plus si je les supprime, mon événement est désactivé et par conséquent ne fonctionne plus.

Que nenni !! L'évenement est désactivé parce que la propriété est resté à False lors de la précédente exécution.

Le script marche très bien quand je rentre une référence à la main.

En effet, je l'ai testé mais en déplacant les instructions en dehors des blocs If.

Par contre il ne fonctionne pas quand je fais un "coller" de plusieurs référence d'un coup !

Ce n'est pas explicite dans ton premier message, que tu souhaite coller plusieurs valeurs en meme temps. Sur la base de ce que tu as déja fait tu devrais pouvoir résoudre le probleme avec une simple boucle.

Par ailleurs ton gestionnaire d'erreur vérifie des conditions qui ont déja été vérifiés. Est ce bien nécessaire?

Pour conclure et si tu ne t'en sors pas essaye ceci:

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
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim plage As Range
    'Détermine les cellules modifiés
    Set plage = Intersect(Target, Range("A2:A100"))   
On Error GoTo errh:
    Application.EnableEvents = False
    If Not plage Is Nothing Then
        With plage.Offset(0, 1)'A droite des cellules modifiés
            .Font.ColorIndex = 1 'Noir par défaut
            'Inscrit la formule dans les cellules
            .FormulaR1C1 = "=VLOOKUP(RC[-1],base_article!R2C1:R100C2,2,0)"
            .Value = .Value 'Supprime les formules
            'Valeurs d'erreurs en rouge
            .SpecialCells(xlCellTypeConstants, xlErrors).Font.ColorIndex = 3
        End With
    End If
errh:
    Application.EnableEvents = True
End Sub

Cordialement,

Tirex28/

[Hindioumax]

Excellent !!
Ton code marche exactement comme je voulais et plus simple

Pas mal la technique, de mettre la formule dans la cellule et de copier son résultat en valeur ! Ca évite entre autre l'erreur retournée par vlookup version vba quand la fonction ne trouve pas de résultat. Il faut la gérer et c'est bien ch...t d'ailleurs soit dit en passant.

Par rapport à mon gestionnaire d'erreur, j'avais rajouté les même lignes car quand je le supprimais le contenu de la colonne ca me mettais des valeurs d'erreur dans d'autre cellule un peu partout.

Quoi qu'il en soit, je n'avais plus de recul sur mon code et je commencais à faire dla bidouille pour que ça fonctionne A PEU PRES ! lol

Merci en tout cas MERCI de ton aide, ca m'enlève le plomb que j'avais dans l'aile !!

[Hindioumax]

ben ma foi, sans vouloir etre plus royaliste que le roi :
je ne peux pas refaire ton probleme sur ma bécane, donc ce qui suit n'est que supposition et non afirmation
tu ne dis pas si tu a essayé ce qui allait dans le sens de ce que je te dis, tu dis juste que ça ne peut pas etre. question, je lance une autre idée ou pas?
bref, passons
si je résume quand tu dis je colle plusieurs données
je traduis
evenement , je colle une donnée
résultat : le code calcul
donc pour trois données
je colle n° 1 quandchange calcule et modifie celulle cible
modif cible = quand change calcul
quand change s'execute
je colle 2 et quandchange calcule et modifie celulle cible
modif cible = quand change calcul le evnts fonctionne mais peut etre qu'il a des limites.
moi je ne t'ai pas proposé une explication mais un embryon de solution. un minimum de politesse consisterai a dire
M.... mais ça colle pas tout a fait a ce que je recherche, ce serais mieux si.. ou j'ai essayé et ça colle pas parce que ..

Désolé Alsimbad, je me suis trop pris la tête sur le script et du coup je ne savais plus comment expliquer mon problème.
Merci de ton aide je ne manquerais pas de m'exprimer plus clairement la prochaine fois