Créer une fonction "find, intersection"

**Phorkys** · 10/01/2016, 16h45

Bonjour à tous,

En me lançant dans ce petit code, j'étais certain d'y arriver, pourtant je bute sur je ne sais quoi, cela ne fonctionne pas.

Dans un tableau, j'ai besoin de trouver une valeur à l'intérieur de la colonne d'en tête, une autre valeur sur la ligne d'en-tête et d'afficher dans ma cellule la donnée à l'intersection de la ligne et de la colonne.
Il m'a parut simple de créer pour cela une fonction, mais j'obtiens une erreur #valeur!...
Qu'est ce qui bug ? Merci pour votre aide.

voici le code :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Function Intersection(Argument1, Argument2, plage1, plage2) As Variant
 
Dim Resultat As Range
Dim x As Range
Dim y As Range
 
Set x = Sheets("Débits").plage1.Cells.Find(Argument1)
Set y = Sheets("Débits").plage2.Cells.Find(Argument2)
 
Resultat = Intersect(Range("x"), Range("y"))
ActiveCell.Value = Resultat.Value
 
End Function

**sogedic** · 10/01/2016, 19h58

Il faudrait typer les arguments en paramètres de la fonction

**gFZT82** · 10/01/2016, 20h05

Bonjour,

Tu peux essayer comme cela.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function Intersection(Argument1, Argument2, Plage1, Plage2) As Variant
Dim x As Range, y As Range
    Set x = Plage1.Find(Argument1, , xlValues, xlWhole)
    If Not x Is Nothing Then
        Set y = Plage2.Cells.Find(Argument2, , xlValues, xlWhole)
        If Not y Is Nothing Then
            Intersection = Application.Intersect(x.EntireColumn, y.EntireRow).Value
        Else
            MsgBox "y non trouvé dans la plage" & Plage2.Address
        End If
    Else
        MsgBox "x non trouvé dans la plage" & Plage1.Address
    End If
End Function

Si la valeur recherchée n'est pas trouvée, un message indiquera cet échec.

Cordialement.

**Phorkys** · 10/01/2016, 21h15

Merci pour votre aide mais pour l'instant, je ne parviens pas à faire fonctionner ce code.
Les deux arguments saisis ne sont pas trouvés dans les lignes et colonnes d'entêtes (alors qu'ils y figurent)
J'ai lu récemment sur le forum quelle était l'utilité de la double négation (If Not x Is Nothing...) mais cela reste encore nébuleux pour moi... je continue mes recherches.

**gFZT82** · 11/01/2016, 09h43

Voici un exemple qui te montrera une application du code que je t'ai donné.
Reste à voir pourquoi tu ne parviens pas a l’adapter à ta situation.
Problème d'arguments ? de type de données ?

Cordialement.

**Menhir** · 11/01/2016, 11h03

Ton truc ne peut pas marcher pour une raison très simple : ton Find renvoie une cellule et non pas une ligne ou une colonne.
Donc, il n'y a pas d'intersection.
Il y a aussi le fait que tu mets une référence de cellule comme argument de Range. Tes variable x et y sont déjà des Range, inutile de les mettre dans un objet Range.

Beaucoup plus simple que de rechercher l'intersection, utiliser les n° de ligne et de colonne :

Code :

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

1
2
Resultat = Cells(x.Row,y.Column)
ActiveCell.Value = Resultat.Value

Comme tu n'as pas dit dans ta question lequel de x et y représenterait la ligne et la colonne, il se peut que tu ais à inverser x et y dans la première ligne.

Cela dit, à mon avis, c'est une mauvaise idée de mettre une action dans une fonction.

**Phorkys** · 11/01/2016, 22h32

Bonsoir,

Merci gFZT82 pour la pièce jointe, c'est exactement ce que je souhaite faire. En réalité les deux arguments issus effectivement de menus déroulants proviennent d'une autre feuille. Les menus déroulants par ailleurs, interdisent la saisie d'une valeur erronée, ce qui je crois rend superflu l'usage des messages d'erreurs. Pour autant, si l'idée est bien celle-ci, le code ne fonctionne pas chez moi.
Je bidouille et j'ai tenté d'inclure au code la proposition de Menhir, mais j'arrive à rien!

Voici ou j'en suis pour l'instant de mes réflexions :

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
Function Intersection(Argument1, Argument2, Plage1, Plage2) As Variant
 
Dim x As Range, y As Range
 
      Set x = Plage1.Cells.Find(Argument1, , xlValues, xlWhole)                                    'plage horizontale (si je comprends bien, Excel cherche mon argument 1 dans la plage 1 et donne cette valeur à x)
            If Not x Is Nothing Then                                                                               ' (là, j'ai plus de mal à saisir la logique, mais c'est si il ne trouve pas la valeur cherchée !)
       Set y = Plage2.Cells.Find(Argument2, , xlValues, xlWhole)                                   'Plage verticale     (Il cherche le deuxième argument dans la plage 2)
            If Not y Is Nothing Then                                                                               ' (idem à + haut, si il ne trouve pas l'argument 2
        Resultat = Cells(y.Row, x.Column)                                                                    ' là ça se corse... j'imagine qu'il part de la position des valeurs trouvées dans les différentes plages et considère la ligne et la colonne correspondante
        ActiveCell.Value = Resultat.Value                                                                      ' La cellule active prends la valeur de "résultat" mais je ne vois pas trop lequel.
         'Intersection = Application.Intersect(x.EntireColumn, y.EntireRow).Value            ' j'ai mis cette ligne en commentaire pour l'instant, mais je la trouve plutôt logique... il trace la colonne et la ligne des points x et y, puis renvoie la valeur de l'intersection.
            Else
       MsgBox "Argument 2 non trouvé dans la plage" & Plage2.Address                         'Messages d'erreurs'
            End If
            Else
       MsgBox "Argument 1 non trouvé dans la plage" & Plage1.Address                         'Messages d'erreurs'
            End If
 
End Function

**Menhir** · 12/01/2016, 09h52

Le rôle d'une fonction est, avant tout, de renvoyer une valeur. Il est déconseillé de s'en servir comme d'une macro pour modifier des éléments d'une feuille.
Donc, pour ce que tu veux faire, par besoin de se faire des noeuds au cerveau :

Code :

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

1
2
3
4
5
6
7
8
9
Function Intersection(Argument1 as Variant, Argument2 as Variant, Plage1 as Range, Plage2 as Range) As Variant
    Dim x As Range, y As Range
 
    Set x = Plage1.Cells.Find(Argument1, , xlValues, xlWhole)
    Set y = Plage2.Cells.Find(Argument2, , xlValues, xlWhole)
    If x Is Nothing Or y Is Nothing Then Exit Function
    Intersection = Cells(y.Row, x.Column).Value
 
End Function

**gFZT82** · 12/01/2016, 10h34

Si le classeur que j'ai joint correspond bien à ce que tu souhaite faire, il te reste à trouver quels sont les éléments de ton classeur qui sont différents.
Le test sur le résultat de la recherche est très utile puisqu'il te permet de voir que le problème se situe à ce niveau.
Le fait que les arguments soient issus d'une autre feuille ne doit pas poser problème.
Par contre, il faut que tu parviennes à comprendre pourquoi les valeurs des arguments 1 et 2 ne sont pas trouvés dans les plages 1 et 2.
Regarde s'il y a bien correspondance des types de données.

Voici le code avec quelques commentaires associés.

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
Function Intersection(Argument1 As String, Argument2 As String, Plage1 As Range, Plage2 As Range) As Integer
Dim x As Range, y As Range
    'On recherche dans la Plage1 la cellule x ayant la valeur de Argument1
    Set x = Plage1.Find(Argument1, , xlValues, xlWhole)
    'Si cette cellule x est trouvée
    If Not x Is Nothing Then
        'On recherche dans la Plage2 la cellule y ayant la valeur de Argument2
        Set y = Plage2.Cells.Find(Argument2, , xlValues, xlWhole)
        'Si cette cellule y est trouvée
        If Not y Is Nothing Then
            'On renvoie la valeur placée à l'intersection de la colonne correspondant à x et de la ligne correspondant à y
            Intersection = Application.Intersect(x.EntireColumn, y.EntireRow).Value
        Else
            'Si la valeur Argument2 n'a pas est trouvée dans Plage2, on emet un message
            MsgBox "y non trouvé dans la plage" & Plage2.Address
        End If
    Else
        'Si la valeur Argument1 n'a pas est trouvée dans Plage1, on emet un message
        MsgBox "x non trouvé dans la plage" & Plage1.Address
    End If
End Function

Cordialement.

**Phorkys** · 13/01/2016, 20h34

Bonsoir gFZT82 et Menhir,

Après une ultime tentative hier soir, c'est en désespoir de cause et un certain dépit que je vous joint mes fichiers.
Je ne doute pas que les solutions que vous avez eu l'un et l'autre l'amabilité de me proposer fonctionnent, mais ce n'est pas le cas chez moi...
D'après mes recherches, 'find' garde en mémoire les dernières indications qu'il a trouvé. D'ou certainement le "if is nothing" qui suit...
Ai-je une fais une erreur dans la conception de mon projet ? ne ne crois pas , vu qu'il est difficile de faire plus simple ! Erreur de compatibilité ?, de version?...
J'avoue ne pas comprendre ce qui bug.

J'avais imaginé un temps travailler avec des plages de cellules nommées, et utiliser l'espace entre les deux noms trouver la valeur à l'intersection des plages, mais comme mes noms de panneaux comportent plusieurs espaces, cela a rendu cette hypothèse de travail infructueuse... Il me reste donc bien la possibilité d'une fonction... sauf que... ça s'avère plus compliqué que je ne l'imaginais. Quoi qu'il en soit, l'apparente simplicité de ce code, a joué avec mes nerfs, ma patience, et mis mes neurones en ébullition... Je vous remercie pour votre patience et votre aide.

**gFZT82** · 13/01/2016, 23h12

Bonsoir,

Test probant avec le premier fichier et la macro suivante

Code :

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

1
2
3
4
5
6
7
Function Intersection(Panneaux As Variant, Epaisseur As Variant) As Variant
Dim x As Range, y As Range
    Set x = Range("C8:C18").Find(Panneaux, , xlValues, xlWhole)
    Set y = Range("D7:H7").Find(Epaisseur, , xlValues, xlWhole)
    If x Is Nothing Or y Is Nothing Then Exit Function
    Intersection = Cells(x.Row, y.Column).Value
 End Function

Je n'ai pas ouvert le deuxième classeur.

Cordialement.

**Menhir** · 14/01/2016, 09h08

Envoyé par Phorkys

Je ne doute pas que les solutions que vous avez eu l'un et l'autre l'amabilité de me proposer fonctionnent, mais ce n'est pas le cas chez moi...

J'évite d'ouvrir les pièces jointes, surtout en xlsm.
Comment as-tu utilisé la fonction que je t'ai transmise ? Plus tu donnes de détails, plus on a de chance de trouver où tu as fais une erreur.

En tout cas ce n'est pas un problème de compatibilité vu que les instructions utilisées sont tout à fait basiques.
Et ça n'a rien à voir non plus avec une "mémoire" de Find.

**Phorkys** · 14/01/2016, 19h48

Bonsoir,

L'instruction figure dans le module 1. Avant la fonction j'ai déclaré "Option explicit"
Le code est celui que tu m'as proposé, légèrement aménagé puisque je me suis rendu compte que mes plages de cellules de recherches n'avaient pas besoin d'être variables.
C'est tout... rien de plus. Comme cela fonctionne chez toi et gFZT82 c'est assez étrange.
Je ferai un essai demain sur l'ordi du boulot !

Soucieux de ne pas vous importuner avec un problème insoluble, je vais cochez la discussion comme résolue et je vous remercie encore tous les deux pour vos interventions qui m'auront permit malgré tout d'y voir plus clair.

Bien cordialement.

**Menhir** · 15/01/2016, 09h11

Envoyé par Phorkys

Le code est celui que tu m'as proposé, légèrement aménagé

Quelles modifications ?

C'est tout... rien de plus.

Comment utilises-tu la fonction dans la feuille de calcul ?

Soucieux de ne pas vous importuner avec un problème insoluble

Ce n'est pas un problème insoluble puisqu'il fonctionne.
Le problème c'est que tu ne comprends pas qu'on travaille en aveugle et que tu es avare de détails pour décrire ce que tu fais.

**Phorkys** · 17/01/2016, 13h47

Bonjour Menhir,

Pour les modifications effectuées, j'ai juste supprimé dans les arguments de la fonction, la notion de plages puisqu'elle ne sont pas variables et que la recherche find s'effectuent sur mes entêtes de lignes et colonnes.
Pour ce qui est de l'utilisation de la fonction, ce que je souhaite, c'est juste que le prix au M2 des panneaux s'affiche dans ma cellule en fonction du type de panneaux choisis et de l'épaisseur. La fonction figure en bout de ligne. Ce sont ces deux informations (épaisseur et type de panneaux) qui à leur intersection, dans le tableau sur la page tarif, me donnent le prix que je souhaite voir s'afficher dans ma cellule. (Pour simplifier, dans le code actuel, tout se trouve sur une seule et même feuille)
Je n'avais pas le sentiment d'être avare d'informations et le problème me parait insoluble car le code semble fonctionner chez vous et pas chez moi. (L'essai fait au boulot sur PC me renvoi l'erreur #nom!)

Je viens de faire une copie d'écran pour illustrer mes propos.

**Phorkys** · 17/01/2016, 14h01

Une autre avec le code...

**Menhir** · 18/01/2016, 09h39

Envoyé par Phorkys

L'essai fait au boulot sur PC me renvoi l'erreur #nom!

Où as-tu enregistré le code de la fonction ?

**gFZT82** · 18/01/2016, 10h08

Bonjour Phorkys,
Salutations Menhir,

Une remarque : dans le fichier Essai tarifs macro.xlsm attaché au message #10, le code est différent de celui présenté dans ton dernier message.

Tu avais écrit :

Code :

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

Intersection = Cells(y.Row, x.Column).Value

alors que l'instruction doit être

Code :

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

Intersection = Cells(x.Row, y.Column).Value

Cordialement.

**Phorkys** · 18/01/2016, 19h09

Bonsoir,

L'erreur que soulève gFZT82 a été corrigée sur le code suivant.
Le code se trouve dans un module de ma seule feuille du fichier nommée "Débits"
Je suis en train de relire le cours proposé sur le site pour les débutants (226 pages) pour voir s'il n'y a pas d'erreurs de déclarations, de portée... mais à priori, j'ai rien trouvé qui cloche...

Si cela peut aider à la compréhension, lors de l'exécution du code de gFZT82 qui contient des messages d'erreurs, quelle que soit la valeur saisie pour les panneaux ou les épaisseurs (qui sont pourtant des choix de liste) le message "x non trouvé dans la plage" apparait.