Chercher numéro dans une plage de cellules non contiguës

**ginofalaci** · 14/09/2018, 11h01

Bonjour,
je bute sur ce problème que je pensait simple à résoudre.

Je cherche un numero (1à10) unique dans une plage de cellules non contigues:

(X5;X9;X15;X19;X26;X30;X36;X40;AA7;AA17;AA28;AA38;P7;P17;P28;P38;L9;L19;L30;L40;AD12;AD33;H14;H35;D18;D39

Si ce numero existe je souhaite afficher la valeur de la colonne adjacente (pour être plus précis, si je cherche 7, et que 7 se trouve en D4, je veux afficher la valeur (texte/nom) de C3)

Je ne souhaite pas utiliser de Macro si cela est possible.

J'ai nommé ces cellules : celtab1 celtab2, celtab3, est-ce qu'on peut faire une recherche dans celtab* quelque chose comme ça?

Merci de votre aide.

**Menhir** · 14/09/2018, 11h42

EQUIV ou RECHERCHEV ne savent pas chercher dans une zone non linéaire (plus d'une colonne ET plus d'une ligne).

Il y a peut-être une possibilité en formules matricielles.

Cela dit, je ne comprends pas trop ton explication sur la position de la valeur à renvoyer par rapport à la valeur trouvée : colonne de gauche et ligne du dessus ?

Invité · 14/09/2018, 11h47

Envoyé par ginofalaci

Bonjour,

Désolé pour le VBA :

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
 
 
Function TrouverValeur(ByVal ValeurATrouver As Integer) As Variant
 
Dim Cellule As Range, AireDeRecherche As Range
 
    Application.Volatile
 
    Set AireDeRecherche = Sheets("Feuil2").Range("X5,X9,X15,X19,X26,X30,X36,X40,AA7,AA17,AA28,AA38,P7,P17,P28,P38,L9,L19,L30,L40,AD12,AD33,H14,H35,D18,D39")
 
    TrouverValeur = ""
    For Each Cellule In AireDeRecherche
        If Cellule = ValeurATrouver Then TrouverValeur = Cellule.Offset(-1, -1)
    Next Cellule
 
    Set AireDeRecherche = Nothing
 
End Function

**ginofalaci** · 14/09/2018, 12h48

Oui c'est ça: colonne de gauche et ligne du dessus.
Pourrait-on imbriquer des INDEX EQUIV avec des OU pour chaque colonne concernée?
mes cellules en question sont dans des colonnes qui ne contiennent pas d'autres valeurs ou formules.
Donc est ce possible/raisonnable de faire un genre de =OU(INDEX EQUIV X5:X40)OU(INDEX EQUIV AA7:AA38) OU(INDEX EQUIV P7

38) etc.. ?

Je vais regarder la macro, merci.

Invité · 14/09/2018, 12h51

Envoyé par ginofalaci

Je n'avais pas fait attention que c'était sur Mac. Sans garantie.

**Menhir** · 14/09/2018, 13h09

En supposant
- que tu ais créé une zone nommée "Test" contenant toutes les cellules de la zone de recherche.
- que la valeur cherchée est en A1
- que cette valeur n'est présente QU'UNE SEULE FOIS dans la zone de recherche.

Essaye ça :

Code :

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

=INDIRECT(ADRESSE(SOMME(SI(Test=A1;LIGNE(Test);0))-1;SOMME(SI(Test=A1;COLONNE(Test);0))-1))

A valider comme formule matricielle avec Ctrl+Shift+Entrée

Je n'ai pas testé dans tous les cas. Donc à vérifier.

**ginofalaci** · 14/09/2018, 13h29

Merci je vais tester.

La macro ne fonctionne pas, sur votre fichier et intégrée dans un autre fichier elle donne une erreur #VALEUR!

Ce genre de 'function' fonctionne sur Mac, j'en utilise d'autres. Je ne vois pas trop pourquoi celle ci ne fonctionnerais pas.
Sauf erreur Mac et Win partagent le même code pour Office, mais certaines fonctions ne sont pas implantées.

Invité · 14/09/2018, 13h32

Envoyé par ginofalaci

N'ayant pas de Mac, je ne pourrai pas vous aider.

Sinon, que devez vous récupérer comme type de valeur ? Modifiez le type de la fonction de Variant à String, ou autre par exemple ....
Insérez de Debug.Print (ou l'équivalent dans Mac) dans le code de la fonction pour voir ce qui ne va pas

**ginofalaci** · 14/09/2018, 16h10

J'ai essayé la macro sur mon windows, et cela fonctionne, je ne comprends pas pourquoi elle ne fonctionne pas sur Mac si quelqu'un a une idée, je suis preneur, car cette Macro me va.

Menhir, j'ai essayé mais ça ne fonctionne pas. Ni sur Mac ni sur Win. Ni si je remplace la zone 'test' par celle qui fonctionne dans la macro 'AireDeRecherche'
J'ai sélectionné chacune des cellules et donner le nom 'test', mais on est d'accord qu'entre ces cellules il y a pas mal de données, mais rien d'autre dans les colonnes contenant ces cellules.

**Menhir** · 14/09/2018, 16h36

Envoyé par ginofalaci

Menhir, j'ai essayé mais ça ne fonctionne pas.

Effectivement, je viens de vérifier, ça ne fonctionne que sur une zone rectangulaire.

**ginofalaci** · 15/09/2018, 15h24

Je cherche toujours la meilleure solution, et je reviens demander si on ne peut pas faire quelque chose avec INDEX EQUIV, vu que ces cellules sont seules dans des colonnes. Il me semblait avoir vu qu'on pouvait utiliser INDEX EQUIV dans une matrice non? https://excel.developpez.com/actu/17...re-Fauconnier/
Mais j'ai dû mal à voir la mise en place.
Sinon j'ai finalement pu faire fonctionner la macro sur mon Mac, en fermant tous les classeurs ouverts avec de multiples classeurs d'essais divers... Mais là j'essaie de la réimplanter dans un fichier qui comporte un tableau plus complet et ça bug de nouveau. Je ne suis pas sûr de la bonne façon de le faire, mais suis assez convaincu que c'est la même méthode que sur Windows.

Invité · 15/09/2018, 15h28

Envoyé par ginofalaci

Bonjour,

Pouvez-vous mettre votre code en ligne et indiquer la ligne où cela plante ?

**ginofalaci** · 15/09/2018, 17h47

Merci de votre intérêt.
Je n'avais pas mis à jour le nom de la feuille.
Le nom comporte un espace dois-je l'écrire ainsi ou il faut rajouter quelque chose?
Sheets("Tableau 1")
Je crois que ça va marcher, je dois ajuster pas mal de petites choses, la cellule à renvoyer n'est pas toujours situé au même endroit par rapport à celle recherchée.
Je vais regarder tout ça tranquillement et je reviendrais vous dire ensuite. Merci en tout cas.

**ginofalaci** · 15/09/2018, 18h42

Petite question au lieu de revoir toute ma mise en page pour aller avec cette macro, j'imagine que c'est possible de séparer le 'range' en deux, pour qu'une partie renvois la cellule colonne de gauche 1 au dessus, et l'autre colonne de droite 1 au-dessus?
voici mes cellules et les deux 'range'

X5,X9,X15,X19,X26,X30,X36,X40,AA7,AA17,AA28,AA38, --> 1 colonne à gauche, 1 ligne au dessus

P7,P17,P28,P38,L9,L19,L30,L40,AD12,AD33,H14,H35,D18,D39 --> 1 colonne à droite, 1 ligne au dessus.

cela réglerait tout mon tableau.

merci

Invité · 15/09/2018, 19h29

Envoyé par ginofalaci

A tester, la fonction agit sur la feuille active. ~~La position par défaut est G.~~

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
 
 
Function TrouverValeur(ByVal ValeurATrouver As Integer, ByVal Position As String) As Variant
 
Dim Cellule As Range, AireDeRechercheGauche As Range, AireDeRechercheDroite As Range
 
 
    Application.Volatile
 
    TrouverValeur = ""
 
    With ActiveSheet
 
         Set AireDeRechercheGauche = .Range("X5,X9,X15,X19,X26,X30,X36,X40,AA7,AA17,AA28,AA38")
         Set AireDeRechercheDroite = .Range("P7,P17,P28,P38,L9,L19,L30,L40,AD12,AD33,H14,H35,D18,D39")
 
         If UCase(Mid(Position, 1, 1)) = "G" Then
            For Each Cellule In AireDeRechercheGauche
                If Cellule = ValeurATrouver Then
                   TrouverValeur = Cellule.Offset(-1, -1)
                End If
            Next Cellule
         Else
            For Each Cellule In AireDeRechercheDroite
                If Cellule = ValeurATrouver Then
                   TrouverValeur = Cellule.Offset(-1, 1)
                End If
            Next Cellule
 
         End If
    End With
 
 
    Set AireDeRechercheGauche = Nothing
    Set AireDeRechercheDroite = Nothing
 
End Function

Pièce jointe 412166

Sinon pour repérer vos cellules :

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
 
 
Sub ChangerLaCouleurDesCellules()
 
Dim Cellule As Range, AireDeRechercheGauche As Range, AireDeRechercheDroite As Range
 
 
    With ActiveSheet
 
         Set AireDeRechercheGauche = .Range("X5,X9,X15,X19,X26,X30,X36,X40,AA7,AA17,AA28,AA38")
         For Each Cellule In AireDeRechercheGauche
             Cellule.Interior.Color = RGB(255, 255, 0)
         Next Cellule
         Set AireDeRechercheDroite = .Range("P7,P17,P28,P38,L9,L19,L30,L40,AD12,AD33,H14,H35,D18,D39")
          For Each Cellule In AireDeRechercheDroite
             Cellule.Interior.Color = RGB(0, 255, 0)
         Next Cellule
 
    End With
 
 
    Set AireDeRechercheGauche = Nothing
    Set AireDeRechercheDroite = Nothing
 
End Sub

**ginofalaci** · 15/09/2018, 21h18

Merci beaucoup, je teste ça d'ici demain.

**ginofalaci** · 16/09/2018, 01h13

Bonsoir, je viens de rentrer et de jeter un oeil, je préfère demander tout de suite avant d'y passer trop de temps, je ne comprends pas ce qu'est "G", la position par défaut.

Invité · 16/09/2018, 03h03

Envoyé par ginofalaci

je ne comprends pas ce qu'est "G", ~~la position par défaut~~.

Il faut indiquer à la fonction quelle série vous souhaitez obtenir le résultat. G : gauche, D : ou plutôt autre que G, à droite. Cela veut dire que s'il n'y a pas le caractère G, c'est la série de droite qui sera prise en référence.
Erreur de ma part, c'est la série de droite qui est prise par défaut.

Invité · 16/09/2018, 05h42

Envoyé par ginofalaci

Si les valeurs saisies ne peuvent se rencontrer plus d'une fois dans les deux aires, alors cette fonction peut être utilisée :

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
 
Function TrouverValeurV2(ByVal ValeurATrouver As Integer) As Variant
 
Dim Cellule As Range, AireDeRechercheGauche As Range, AireDeRechercheDroite As Range
 
    Application.Volatile
 
    TrouverValeurV2 = ""
 
    With ActiveSheet
 
         Set AireDeRechercheGauche = .Range("X5,X9,X15,X19,X26,X30,X36,X40,AA7,AA17,AA28,AA38")
         Set AireDeRechercheDroite = .Range("P7,P17,P28,P38,L9,L19,L30,L40,AD12,AD33,H14,H35,D18,D39")
 
         For Each Cellule In AireDeRechercheGauche
                If Cellule = ValeurATrouver Then
                   TrouverValeurV2 = Cellule.Offset(-1, -1)
                   Exit Function
                End If
            Next Cellule
            For Each Cellule In AireDeRechercheDroite
                If Cellule = ValeurATrouver Then
                   TrouverValeurV2 = Cellule.Offset(-1, 1)
                End If
            Next Cellule
 
    End With
 
    Set AireDeRechercheGauche = Nothing
    Set AireDeRechercheDroite = Nothing
 
End Function

**ginofalaci** · 16/09/2018, 11h09

Je vous remercie, je pense que ça va marcher, je dois travailler un peu dessus pour l'adapter, car la distance vers les valeurs à renvoyer augmente plus on va vers la gauche et plus on va vers la droite. Mais je crois que j'ai compris le système, et que je devrais pouvoir détailler plus ma selection de cellules, pour incrémenter les positions à renvoyer, en ajoutant AireGauche1, AireGauche2 , AireDroite1, AireDroite2 et autant de autant de Cellule.Offset(-1,1), Cellule.Offset(-2,1) etc... Je reviens quand j'ai fini ou si je bloque vous redemander un conseil.

Chercher numéro dans une plage de cellules non contiguës [XL-MAC 2016]

Excel

Discussions similaires

Partager

Partager