XL 2016 Recherche dans plusieurs onglets

Calvin

XLDnaute Nouveau
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.
 
Solution
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" :
VB:
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

XLDnaute Nouveau
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
 

Pièces jointes

  • Semainier.xlsx
    37.9 KB · Affichages: 13

dionys0s

XLDnaute Impliqué
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.

VB:
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
 

eastwick

XLDnaute Impliqué
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
 

job75

XLDnaute Barbatruc
Bonjour Calvin, eastwick, dionys0s,

Voyez le fichier .xlsm joint et cette fonction VBA :
VB:
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+
 

Pièces jointes

  • Semainier(1).xlsm
    46.1 KB · Affichages: 5

job75

XLDnaute Barbatruc
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" :
VB:
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".
 

Pièces jointes

  • Semainier(2).xlsm
    48.4 KB · Affichages: 10

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 885
Membres
101 830
dernier inscrit
sonia poulaert