[VBA] Récupérer la valeur d'une cellule après une recherche

Bonjour à tous,
Je suis assez peu expérimentée en VBA mais j'arrive d'habitude à bricoler quelques petites macros qui m'aident au quotidien.
Mais là, je bloque sur une chose assez banale, j'ai passé deux soirées dessus et je ne comprends pas ce qui cloche dans mon code.

Ma macro doit chercher une valeur dans la colonne A de mon tableau (un code postal) et placer dans la variable ville la valeur de la cellule B de la même ligne (la ville correspondante).
Ma feuille contient 37193 lignes (eh oui il y a beaucoup de villes en France).

Pour tester les valeurs, j'ai écrit le petit programme suivant qui fonctionne très bien:
(dans cet exemple admettons que je cherche le code postal 90000)

Code:

---

1 2 3 4 5

With Worksheets("CP").Range("A2:A39173") Set codep = .Find("90000", LookIn:=xlValues) MsgBox "Ligne: " & codep.Row Msgbox "Colonne: " & codep.Column + 1 End With

---

Msgbox me rapporte bien la ligne 31178 (juste) et la colonne 2 (juste puisque je me place immédiatement dans la colonne qui contient les noms des villes).

Mais quand je veux stocker les données de la cellule, là ça ne fonctionne plus:

Code:

---

1 2 3 4

With Worksheets("CP").Range("A2:A39173") Set codep = .Find("90000", LookIn:=xlValues) ville = Cells(codep.Row, codep.Column + 1).Value End With

---

Et là >>> ma variable reste désespérément vide... Aucun message d'erreur.

Pour continuer les tests j'ai demandé à Excel de sélectionner la cellule demandée:

Code:

---

Cells(codep.Row, codep.Column + 1).Select

---

et j'ai cette fois une erreur d'exécution 1004 : la méthode Select de la classe Range a échoué.

Je n'arrive pas à comprendre ce que je fais de travers...
Merci de m'avoir lue, et merci d'avance pour votre aide :)
Bien cordialement

Salut

Parce qu'il faut toujours (si on ne veut pas d'ennui) spécifier sur quelle feuille on travaille ;) et aussi le classeur.

Ici Cells(....) est traduit par VBE par ActiveSheet.Cells(....), il faut préciser sur quelle feuille on veut prendre l'info pour ne pas laisser VBE décider tout seul

Code:

---

1 2 3 4 5 6 7 8

With Thisworkbook.Worksheets("CP") Set codep = .Range("A2:A39173").Find("90000", LookIn:=xlValues) 'On s'assure que le code est trouvé if not codep is nothing then ville = .Cells(codep.Row, codep.Column + 1).Value 'Ou alors plus court if not is nothing then ville = codep.offset(0,1).value End With

---

A la place de Thisworkbook.Worksheets("CP") il est possible d'utiliser le codename de la feuille, c'est la propriété (Name) dans l'image suivante
Pièce jointe 537449
++
Qwaz

Bonsoir Qwazerty et merci de ta précieuse aide !
En effet mon semblant de code doit sembler être une hérésie pour les connaisseurs, et j'avoue me perdre un peu entre les appels de classeurs et feuilles en VBA :mouarf:

Apparemment ce qui le dérangeait dans mon code est de ne pas appeler la feuille "CP" lorsque je voulais affecter la valeur à ma variable.
J'ai donc laissé ton "intro" ThisWorkBook(...) mais le problème était toujours présent.
Il a carrément fallu que je fasse le forcing dans la ligne de la variable avec

Code:

---

ville = ThisWorkbook.Worksheets("CP").Range("B" & codep.Row).Value

---

Et là ça marche !!!!
Un grand merci à toi, je saurai me souvenir de mes bêtises et préciser mes feuilles maintenant :)

Hum, je ne comprends pas pourquoi le code que j'ai mis au dessus ne fonctionne pas, ça t'éviterait d'avoir des ligne de code longue comme un jour sans fin

Citation:

---

Apparemment ce qui le dérangeait dans mon code est de ne pas appeler la feuille "CP" lorsque je voulais affecter la valeur à ma variable.

---

Oui c'est pour ça que j'ai mis .Cells avec le point avant Cells

Mais je préférerai utiliser la ligne if not is nothing then ville = codep.offset(0,1).value à la place.

N'hésite pas à proposer ta macro pour voir ce qui peut être amélioré

++
Qwaz

Je n'ai pas pu faire autant d'essais que je l'aurais voulu hier soir par manque de temps mais sauf erreur de ma part cela ne suffisait pas.
Après j'ai peut être trop simplifié mon code pour le mettre sur le forum ce qui pourrait expliquer que certains éléments manquent à ton "enquête" :mouarf:

Le résumé de ma macro, c'est que je tape un code postal dans une autre feuille, et quand je valide la cellule cela lance la macro qui va chercher (avec la variable chercher justement) la ville qui correspond. Plus tard je ferai aussi en sorte que la macro me crée une liste en cas de villes multiples (parfois le même code postal correspond à plusieurs villes), mais pour le moment je me contenterai très bien de la situation présente.

Donc au final mon code fonctionnel correspond à

Code:

---

1 2 3 4 5 6 7 8 9

'Worksheet on Change If Target.Address = "$C$15" And Target.Value <> "" Then chercher = Target.Value With ThisWorkbook.Worksheets("CP") Set codep = .Range("A2:A39173").Find(chercher, LookIn:=xlValues) If Not codep Is Nothing Then ville = ThisWorkbook.Worksheets("CP").Cells(codep.Row, codep.Column + 1).Value End With Cells(16, 3).Value = ville End If

---

Cela dit ne te creuse pas trop la tête dessus, le code est fonctionnel et tant pis pour l'optimisation :mouarf: je le mets surtout ici si cela peut dépanner quelqu'un dans la même situation que moi.

Encore merci !

Salut

Vu la description de la situation, il n'est pas besoin de passer par VBA pour faire ça, Excel est un tableur est possède des outils pour gérer ce genre de cas.
Index + Equiv sont l'un d'eux, tu trouveras un exemple en PJ.

++
Qwaz

Super merci de l'info ! C'est vrai que j'ai tendance à avoir trop recours aux macros alors que dans certains cas des solutions existent déjà.
Je ferai quelques tests tranquillement ce week-end avec index et equiv, ça a l'air d'être exactement ce qu'il me faut :)