XL 2016 Recherche dans plusieurs onglets

[Calvin]

Bonjour,
j'aimerais pouvoir rechercher une valeur parmi une colonne de plusieurs onglets et que cela renvoie le nom de l'onglet (ou une cellule de l'onglet).
Et si la valeur est présente dans plusieurs onglets, que cela donne un message d'erreur.
Merci d'avance.

 

[eastwick]

Bonjour, un fichier serait le bienvenu. Votre requête est trop abstraite et les possibilités infinies...

 

[Calvin]

Pardon,
il faudrait que les cours qui apparaissent en colonne C des onglets SEM X soient référencés en colonne M de l'onglet 200i en nommant la SEM ou en informant que le cours existe plusieurs fois

 

[dionys0s]

Bonjour Calvin, bonjour eastwick,

voici une fonction qui recherche dans tous les onglet d'un classeur et qui renvoie une plage uniquement si la valeur cherchée est unique dans tout le classeur. Elle peut être affinée mais constitue je pense une base de travail.

Public Function FindCustom(ByVal FindValue As Variant) As Excel.Range

  Dim wks As Excel.Worksheet
  Dim rngResult As Excel.Range

  For Each wks In ThisWorkbook.Worksheets
    Set rngResult = wks.UsedRange.Find( _
      What:=FindValue, _
      LookIn:=XlFindLookIn.xlValues, _
      LookAt:=XlLookAt.xlWhole, _
      SearchOrder:=XlSearchOrder.xlByRows, _
      SearchDirection:=XlSearchDirection.xlNext, _
      MatchCase:=True)

    If Not rngResult Is Nothing Then
      If FindCustom Is Nothing Then
        Set FindCustom = rngResult

        If Not wks.UsedRange.FindNext(FindCustom) Is Nothing Then
          If wks.UsedRange.FindNext(FindCustom).Address <> FindCustom.Address Then
            Call MsgBox("Erreur : valeur en doublon dans l'onglet.")
            Set FindCustom = Nothing: Exit Function
          End If
        End If
      Else
        Call MsgBox("Erreur : valeur déjà trouvée dans un autre onglet")
        Set FindCustom = Nothing: Exit Function
      End If
    End If
  Next wks

End Function

Bonne journée à tous

 

[Calvin]

Merci mais n'ayant jamais créé de macro, c'est trop compliqué pour moi de la mettre en oeuvre.
Merci quand même, bonne journée.

 

[eastwick]

La colonne L de l'onglet 200i impose la semaine. Pourquoi alors aller chercher dans les autres semaines ?
Pour être plus clair, les lignes 7 et 8 vont faire une recherche dans la semaine 1, les lignes 9 et 10 dans la semaine 2, la ligne 11 la semaine 3 etc... Une fois les informations rapatriées on voit s'il y a doublon, non ? Avec une coloration automatisée par exemple

 

[Calvin]

Oui vous avez raison sauf que la colonne L est ce qui est prévu et pas forcément ce qui va se faire, c'était pour vérifier que la réalité soit conforme ou différente de l'attendu.
Merci quand même.

 

[eastwick]

un exemple : CAM 3 apparait dans SEM 1 et SEM 2, vous voulez donc que cette info apparaisse en cellule M9 onglet 200i ? Là il y a doublon en principe...

 

[Calvin]

Exactement et si pas de doublon, faire apparaître le N° de la semaine

 

[job75]

Bonjour Calvin, eastwick, dionys0s,

Voyez le fichier .xlsm joint et cette fonction VBA :

Function SEM(cours As String, separateur As String)
Application.Volatile
Dim w As Worksheet, nom As String
For Each w In Worksheets
    nom = UCase(w.Name)
    If nom Like "SEM*#" Then If Application.CountIf(w.Columns(3), cours) _
        Then SEM = SEM & separateur & Val(Replace(nom, "SEM", ""))
    Next
SEM = Mid(SEM, Len(separateur) + 1)
If IsNumeric(SEM) Then SEM = Val(SEM)
End Function

Le code doit être placé impérativement dans un module standard.

Formule en M7 de la feuille 200i =SEM(A7;"-") à tirer vers le bas.

La fonction étant volatile les formules sont recalculées quand on modifie une cellule quelconque.

Le séparateur "-" peut être modifié comme on veut.

A+

 

[job75]

En fait je n'aime pas les fonctions volatiles car elles se recalculent bien trop souvent et ce peut être gênant.

Dans le code de la fonction de ce fichier (2) j'ai supprimé Application.Volatile.

En contrepartie j'ai ajouté cette macro dans le code de la feuille "200i" :

Private Sub Worksheet_Activate()
UsedRange.Columns(1) = UsedRange.Columns(1).Value 'les formules en colonne M sont recalculées
End Sub

Les formules en colonne M sont recalculées seulement quand on active la feuille.

Pour tester modifiez des données dans la plage A6:B22 de "SEM 1".

 

[Calvin]

Merci beaucoup Job75, ça marche mais je n'arrive pas à accéder à la macro pour pouvoir changer certains détails

 

[job75]

Surtout ne rien changer, surtout quand on est un bleu.

 

[Calvin]

C'est vrai mais je voulais nommer les onglets SEM (1) SEM (2) ...

 

[job75]

C'est vrai mais je voulais nommer les onglets SEM (1) SEM (2) ...

Cela ne fera que compliquer inutilement les choses, restez avec SEM 1 et SEM2 ou SEM 01 et SEM 02.