Discussion :

[XBS17]

Bonjour à tout le monde,

J'ai un problème avec une fonction que j'utilise comme formule dans une feuille de calcul. J'ai fait une recherche sur le forum mais je ne trouve pas de sujet relatif à mon problème.

J'ai une feuille "Data" avec 3 colonnes.
La première colonne est code1, les cellules de cette colonne contiennent un caractère: A, B, C, D, ...
La deuxième colonne est code2, les cellules de cette colonne contiennent un caractère: a, b, c, d, ...
La troisième colonne est valeur, il s'agit d'une valeur numérique.

Les combinaisons code1 + code2 sont uniques.

Sur une autre feuille, j'introduis dans une cellule la valeur "A" pour code1 et dans une autre cellule "c" pour code2, j'aimerai que ma fonction me retourne la valeur qui correspond à la combinaison A c qui est définie dans la feuille "data".

Ci-dessous, mon code:

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
Function Rchval(Texte1, Texte2)
 
Dim CelluleTrv As Range         'Cellule trouvé lors de la recherche
Dim FirstAddress As String      'Adresse de la première cellule trouvée
Dim Plage As Range
 
Set Plage = Worksheets("Data").Columns(1)
Set CelluleTrv = Plage.Find(Texte1)
 
If Not CelluleTrv Is Nothing Then
    FirstAddress = CelluleTrv.Address 'Initialisation de l'adresse de la première cellule trouvée
    Do 'Boucle pour analyser les autres résultats trouvés
        If Worksheets("Data").Cells(CelluleTrv.Row, 2).Value = Texte2 Then
            Valeur = Worksheets("Data").Cells(CelluleTrv.Row, 3).Value 'Valeur de la cellule trouvée
        End If
 
        Set CelluleTrv = Plage.FindNext(CelluleTrv) 'Recherche les autres résultats
    Loop While CelluleTrv.Address <> FirstAddress 'Continue la boucle tant que l'adresse est différent de la première adresse trouvée
End If
 
Rchval = Valeur
 
End Function

Si j'utilise, par exemple, la formule =RCHVAL(A1;B1) dans n'importe quelle cellule, j'ai le message d'erreur #valeur!
Si j'utilise la fonction dans une procédure sub, j'obtiens le bon résultat.

Pour info, la fonction est bien définie dans un module et non pas dans le code d'une feuille de calcul ou dans ThisWorkbook.

J'ai essayé de Debugger, j'ai mis des points d'arrêt dans le code.
Il semble que ça coince au niveau du Set CelluleTrv = Plage.FindNext(CelluleTrv) , CelluleTrv devient Nothing , alors qu'une même valeur est présente plusieurs fois dans la colonne1.

Je ne comprends pas ce qui se passe. Je commets peut-être une grossière erreur! Bref, je suis coincé et je sollicite votre assistance.

Merci d'avance pour vos réponses.

[SfJ5Rpw8]

Bonjour,



Que doit retourner ta fonction ? tu écris plusieurs fois le résultat dans la variable valeur (que par ailleurs tu n'as pas déclaré) .. et donc seul le dernier résultat devrait être pris en compte ...


pour le findNext il semble ne pas fonctionner dans une fonction personnalisée..

dans la note MS :
http://support.microsoft.com/kb/170787/fr

je dirais que cela correspond à : "Définir des propriétés ou exécuter la plupart des méthodes."

..

tu devrais peu-être remplacer ce find par une boucle dans ces cellules..

[XBS17]

Bonjour Bbil,

Tout d'abord merci pour ta réponse.

La fonction doit retourner la valeur numérique qui correspond à la combinaison code1 + code2 définie dans la colonne 3 sur la feuille "Data".

Je ne connaissais pas les limitations des fonctions personnalisées.
J'ai consulté le lien que tu donnes dans ton message et je partage ton avis sur le findnext. C'est vraiment dommage qu'il existe cette limitation.

Comme tu le conseilles, je vais abandonner le find et passer par des boucles for.
Initialement, je pensais que la solution : fonction personnalisée + find serait une solution facile ... hé ben, je ne pensais pas arriver dans cette voie sans issue.
Il y a aussi la piste événement et procédures sub à explorer ....

Encore merci pour ton aide!

[SfJ5Rpw8]

La fonction doit retourner la valeur numérique qui correspond à la combinaison code1 + code2 définie dans la colonne 3 sur la feuille "Data".

cela n'explique pas pourquoi tu recherche plusieurs valeurs ? et comment tu compte traiter ...




En faisant une recherche sur des sites anglophone j'ai vu qu'une solution utilisée était l'appel successif de la fonction Find .. en modifiant le paramètre After à chaque nouvel appel ... à creuser ..

[XBS17]

Bonjour Bbil,

Mon tableau Data se présente de la manière suivante:

[Code1]-[Code2]-[Valeur]
[A]-[a]-[10]
[A]-[b]-[20]
[A]-[c]-[30]
[A]-[d]-[40]
[B]-[a]-[90]
[B]-[b]-[80]
[B]-[c]-[70]
[B]-[d]-[60]

Sur une autre feuille, l'utilisateur complète Code1 et Code2 dans 2 cellules.
En retour, il reçoit la valeur numérique dans la cellule où est la fonction personnalisée.

Ma feuille "Data" comporte naturellement beaucoup plus de données que dans l'exemple ci-dessus.

Exemple:
L'utilisateur introduit dans deux cellules les valeurs: B et c
Code1: B
Code2: c

La fonction recherche B dans la colonne 1 de la feuille "Data". Il y a plusieurs résultats.
Pour chaque résultat, je vérifie si la valeur de la colonne 2 correspond au code2, ici, c'est la valeur c.
Les combinaisons de codes sont uniques.
Donc normalement, la fonction renvoie une seule valeur, 70.

Je vais creuser l'astuce que tu viens de donner avec des find successifs, c'est une piste intéressante.

Encore merci pour ces précieuses informations

[XBS17]

Bonjour,

Suite aux conseils de Bbil, j'ai remplacé la ligne:

Code :

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

Set CelluleTrv = Plage.FindNext(CelluleTrv)

par

Code :

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

Set CelluleTrv = Plage.Find(Texte1, after:=CelluleTrv)

et ça fonctionne!!!

Bref, ci-dessous le code corrigé.

Merci à Bbil

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
Function Rchval(Texte1, Texte2)
 
Dim CelluleTrv As Range         'Cellule trouvé lors de la recherche
Dim FirstAddress As String      'Adresse de la première cellule trouvée
Dim Plage As Range
Dim Valeur As Double
 
Set Plage = Worksheets("Data").Columns(1)
Set CelluleTrv = Plage.Find(Texte1)
 
If Not CelluleTrv Is Nothing Then
    FirstAddress = CelluleTrv.Address 'Initialisation de l'adresse de la première cellule trouvée
    Do 'Boucle pour analyser les autres résultats trouvés
        If Worksheets("Data").Cells(CelluleTrv.Row, 2).Value = Texte2 Then
            Valeur = Worksheets("Data").Cells(CelluleTrv.Row, 3).Value 'Valeur de la cellule trouvée
        End If
 
        'Set CelluleTrv = Plage.FindNext(CelluleTrv)
            'Ne fonctionne pas si utilisé sous forme de formule dans une feuille de calcul
            '(limitations des fonctions personnalisées: "ne peut pas effectuer les opérations suivantes :
            'Définir des propriétés ou exécuter la plupart des méthodes."
 
        Set CelluleTrv = Plage.Find(Texte1, after:=CelluleTrv) 'Recherche les autres résultats
 
    Loop While CelluleTrv.Address <> FirstAddress 'Continue la boucle tant que l'adresse est différent de la première adresse trouvée
End If
 
Rchval = Valeur
 
End Function

[Philippe Tulliez]

Bonjour,
Si la concaténation des deux codes est unique il n'est pas obligatoire de passer par une fonction personnalisée.
Imaginons un liste de données plage A2:G6, la plage A1:G1 contenant les étiquettes de colonnes.
En colonne B, le nom, colonne C le prénom et en colonne D, l'adresse
En cellule D9 l'a formule pour obtenir l'adresse de la personne dont le nom est encodé en cellule B9 et le prénom en cellule C9

Code :

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

=INDEX($A$2:$G$6;SOMMEPROD(($B$2:$B$6=$B$9)*($C$2:$C$6=$C$9)*(LIGNE(A2:A6)-1));EQUIV(D8;$A$1:$G$1;0))

Bien entendu, il est possible de placer cette formule pour en faire une fonction personnalisée.

[XBS17]

Bonjour Philippe,

Merci pour la solution sous forme de formule.
Je l'ai testée et ça fonctionne pour ce que je souhaite faire.
J'ai juste apporté une petite correction:
=INDEX($A$2:$G$6;SOMMEPROD(($B$2:$B$6=$B$9)*($C$2:$C$6=$C$9)*(LIGNE(A2:A6)-1));EQUIV(D1;$A$1:$G$1;0))

Je dois avouer que je ne suis pas familier de ce genre de formule ... c'est un peu plus "cérébral"

[Philippe Tulliez]

Bonjour,

Le premier argument de la formule EQUIV était bien chez moi D8 mais j'avais effectivement oublié de le préciser dans ma réponse, la cellule D8 contient dans mon exemple le texte Adresse ce qui me permet de rendre dynamique le renvoie de l'information. La formule EQUIV renvoyant la position de la valeur cherchée dans une plage de cellules (organisée soit en colonne, soit en ligne),

[XBS17]

Merci Philippe pour la précision.
Je comprends maintenant l'intérêt du Equiv dans la formule.

Conclusion:
Mon problème est résolu.
Merci à Bbil et à Philippe.

Je marque problème RESOLU