recherche sur plusieurs feuilles

[pescalune]

bonjour,
j'ai un classeur de 150 feuilles identiques (clients) avec dans la colonne A ; lignes 2 à 150 des numéros de commande;

mon problème : comment faire pour que si je tape en D 1 de la feuille 151 un numéro de commande, le nom de la feuille ou est cet enregistrement apparaisse en D 2.

D avance merci pour votre aide

pescalune

 

[Robert]

Re : recherche sur plusieurs feuilles

Bonjour Pescalune, bonjour le forum,

Je te propose cette macro événementielle à placer par VBE dans l'objet Feuil151 :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As Byte 'décalre la variable f
Dim li As Byte 'déclare la variable li
Dim nc As Variant 'déclare la variable nc
 
If Target.Address <> "$D$1" Then Exit Sub 'si le changement n'a pas lieu en D1, sort de la procédure
If Target.Value = "" Then Target.Offset(1, 0).Value = "": Exit Sub 'si on efface D1, D2 est éffacé aussi
 
nc = Target.Value 'définit la variable nc
 
For f = 1 To 150 'boucle 1 : sur 150 onglets
    With Sheets(f) 'prend en compte l'onglet de la boucle
 
        'boucle 2 : sur les lignes 2 à 150 de l'onglet pris en compte
        For li = 2 To 150
            If .Cells(li, 1).Value = nc Then 'condition : si la valeur de la cellule correspond au numéro édité en D1
                ActiveSheet.Range("D2") = Sheets(f).Name 'écrit en D2 le nom de l'onglet correspondant
                GoTo fin 'sort de la procédure via la balise "fin"
            End If 'fin de la condition
        Next li 'prochaine ligne de la boucle 2
 
    End With 'fin de la prise en compte de l'onglet
 
Next f 'prochain onglet de la boucle 1
 
MsgBox "ce numéro de facture n'existe pas" 'ce message apparaît si tous les onglets on été vérifiés
ActiveSheet.Range("D1").Select 'sélectionne la cellule D1 (pour une éventuelle nouvelle édition ou pour être effacée)
 
fin: 'balise
 
End Sub

 

[pescalune]

Re : recherche sur plusieurs feuilles

Bonjour Pescalune, bonjour le forum,

Je te propose cette macro événementielle à placer par VBE dans l'objet Feuil151 :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As Byte 'décalre la variable f
Dim li As Byte 'déclare la variable li
Dim nc As Variant 'déclare la variable nc
 
If Target.Address <> "$D$1" Then Exit Sub 'si le changement n'a pas lieu en D1, sort de la procédure
If Target.Value = "" Then Target.Offset(1, 0).Value = "": Exit Sub 'si on efface D1, D2 est éffacé aussi
 
nc = Target.Value 'définit la variable nc
 
For f = 1 To 150 'boucle 1 : sur 150 onglets
    With Sheets(f) 'prend en compte l'onglet de la boucle
 
        'boucle 2 : sur les lignes 2 à 150 de l'onglet pris en compte
        For li = 2 To 150
            If .Cells(li, 1).Value = nc Then 'condition : si la valeur de la cellule correspond au numéro édité en D1
                ActiveSheet.Range("D2") = Sheets(f).Name 'écrit en D2 le nom de l'onglet correspondant
                GoTo fin 'sort de la procédure via la balise "fin"
            End If 'fin de la condition
        Next li 'prochaine ligne de la boucle 2
 
    End With 'fin de la prise en compte de l'onglet
 
Next f 'prochain onglet de la boucle 1
 
MsgBox "ce numéro de facture n'existe pas" 'ce message apparaît si tous les onglets on été vérifiés
ActiveSheet.Range("D1").Select 'sélectionne la cellule D1 (pour une éventuelle nouvelle édition ou pour être effacée)
 
fin: 'balise
 
End Sub

Robert,
suis désolé mais:
VBE et chinois c'est kifkif pour moi ....
et les 150 feuilles portent en réalité le nom du fournisseur
car j'attendais(innocemment)une réponse plus facile d'emploi, genre formule de recherche...
merci mille fois d'un vieux (52 balais) voisin de Lunel, guitariste et pantouflard.....
pescalune

 

[Robert]

Re : recherche sur plusieurs feuilles

Bonjour Pescalune, bonjour le forum

Ok je vais traduire le chinois alors.
Voici la procédure à suivre à la lettre.
1. Sélectionne et Copie le code que je t'ai donné
2. Sélectionne l'onglet 151.
3. Reclique sur cet onglet mais cette fois-ci avec le bouton droit de la souris. Dans le menu contextuel choisis l'option Visualiser le code.
4. L'éditeur Visual Basic s'ouvre. Colle-s-y le code.
5. Referme Visual Basic Editor avec [Alt]+[F11].
6. Enregistre sous (on ne sait jamais si après ça ne te convient pas)

Ça y est fait un essai ça devrait marcher. Comme ce fichier contient désormais du code (Macro) tu auras un message à l'ouverture de celui-ci te demandant d'activer les macros. Ce message un peu alarmant est tout a fait normal. Tu n'as rien à craindre pour ton PC.

Bonne Continuation voisin grateux à pantoufles...

 

[linum1]

Re : recherche sur plusieurs feuilles

Bonjour,

j'aimerai savoir s'il est possible d'adapter ce code pour une utilisation similaire.

J'ai une feuille de 2800 et quelques lignes, et plusieurs onglets (68) dans lesquels je dois en théorie retrouver toutes les lignes du premier onglet. Or ce n'est pas le cas, il en manque 100 environ.

Je voulais essayer d'adapter ce code mais j'obtiens l'erreur '6' : dépassement de capacité. J'imagine qu'il faut changer la nature de la variable nc, mais je ne sais pas quel sera son nom (je précise que la valeur inchangée de chaque ligne est un nombre au format text...). Serait-il possible de changer l'apparition du MsgBox pour un message en 'Di' de manière à appliquer la macro sur chaque ligne i en un seul coup ?

N'ayant pas assez de temps pour apprendre les rudiments du VBA, j'espère que quelqu'un pourra m'aider !

Merci d'avance.

 

[Robert]

Re : recherche sur plusieurs feuilles

Bonjour Linum, bonjour le forum,

Coïncidence de retomber sur une de mes interventions...
Le dépassement de calacité viens plutôt de la variable li que tu devras transformer en Integer voire même Long pour être tranquille. Pour le message j'ai pas vraiment compris ce que tu voulais. Je te propose de mettre un exemple allégé de ton fichier (avec la même structure et quelques données) pour que je puisse l'adapter au mieux.

 

[linum1]

Re : recherche sur plusieurs feuilles

Bonjour Robert et merci d'avoir répondu aussi vite.

Je poste en pièce jointe le fichier allégé. Dans cet exemple, le mandat n°000003 n'a pas été classé dans les onglets créés. Je souhaiterais pouvoir retrouver le (ou les) mandat manquant dans les onglets à partir de la liste complète de l'onglet 'mandats'.

L'idéal serait d'afficher "non trouvé" dans une colonne de la feuille 'Mandats' lorsque le mandat de la ligne correspondante est absent des autres onglets.

Le problème de la procedure que vous aviez indiqué c'est qu'elle ne traite qu'une valeur par une valeur. Or j'aimerai pouvoir traiter les 2800 lignes en une seule fois puis trier les lignes pour retrouver les mandats manquants.

J'espère avoir été clair 😱 et merci pour le temps que vous consacrerez à ce petit problème !

 

[Robert]

Re : recherche sur plusieurs feuilles

Bonjour Linum, bonjour le forum,

La macro ci-dessous devrait te convenir :

Sub Macro1()
Dim cel As Range 'déclare la variable cel (CELlule)
Dim sh As Worksheet 'déclare la variable sh (SHeet)
Dim r As Range 'déclare la variable r (Recherche)
 
'boucle 1 sur toutes les cellules de la colonne B de l'onglet "Mandats"
For Each cel In Sheets("Mandats").Range("B2:B" & Sheets("Mandats").Range("B65536").End(xlUp).Row)
    For Each sh In Sheets 'boucle 2 sur tous les onglets du classeur
        If sh.Name <> "Mandats" Then 'condition : si l'onglet a un autre nom que "Mandats"
            Set r = sh.Columns(2).Find(cel.Value, sh.Range("B1"), xlValues, xlWhole) 'définit la variable r
            If Not r Is Nothing Then GoTo suite 'si il existe au moins une occurrence de r, va à l'étiquette "suite"
        End If 'fin de la condition
    Next sh 'prochain onglet du classeur (boucle 2)
    'si le code arrive ici c'est que le mandat n'a pas éé trouvé nulle part...
    MsgBox "le mandat " & cel.Value & " n'a pas été classé !" 'message
    cel.Interior.ColorIndex = 3 'colorie la cellule en rouge (tu supprimeras si ça ne vas pas)
 
suite: 'étiquette
Next cel 'prochaine cellule de la boucle 1
End Sub

 

[BOISGONTIER]

Re : recherche sur plusieurs feuilles

Bonjour,

Avec formules Voir PJ

http://boisgontierjacques.free.fr/pages_site/sommeprod.htm#Rech3D

JB

 

[linum1]

Re : recherche sur plusieurs feuilles

Merci à vous deux,

je viens d'essayer d'executer la macro dans mon classeur et elle n'a pas donné de résultats. J'ai oublié de préciser qu'il y avait deux autres onglets, mais dans lesquels je suis sûr que le numéro de mandat n'apparaît pas, donc cela ne doit pas poser de problème (j'ai aussi testé en supprimant ces deux ongets).

En tous cas, aucune MsgBox n'apparaît, et aucune cellule n'est colorée (résultats que j'ai pu observer avec ma feuille 'exemple'). Peut être que tous les mandats sont présents, mais le total des montants différe lorsque j'ajoute celui de chacune des feuilles et lorsque je fais la somme sur l'onglet mandat... Je vais y réfléchir, l'erreur vient peut être d'ailleurs !

Concernant l'usage des fonctions excel, lorsque je les lance dans la feuille exemple elles ne fonctionnent plus et je n'ai pas encore compris comment les appliquer à mes 68 onglets et 2827 lignes ! Mais je vais voir comment elles marchent et essayer de les appliquer.

Merci encore pour votre support !

 

[Robert]

Re : recherche sur plusieurs feuilles

Bonjour le fil, bonjour le forum,

La macro que je te propose fonctionne pour tous les onglets sauf mandats. Elle lance la recherche dans la colonne B donc vérifie que dans ton fichier original les mandats sont bien tous en colonne B.Sinon il faut modifier mais là ça devient compliqué.

 

[linum1]

Re : recherche sur plusieurs feuilles

Merci,

c'est ce que j'avais compris en lisant vos annotations dans le code, je ne comprends juste pas pourquoi cela ne marche pas. J'ai pu vérifier qu'il manquait une centaine de mandats puisque la somme des lignes des onglets est différente de 2827 et que le montant total ne correspond pas.

Si la macro n'indique pas de mandat manquant je ne vois pas d'où peut venir le problème...

Sans être à ma place j'imagine qu'il est difficile de m'aider plus, alors je vous remercie déjà grandement pour l'aide fournie jusqu'à présent, et je vais essayer autre chose !