XL 2016 Recherche dans plusieurs onglets

eastwick

XLDnaute Accro

• 27 Mars 2021

• #2

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

 

Vote positif 0 Vote négatif

C

Calvin

XLDnaute Nouveau

• 27 Mars 2021

• #3

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

Vote positif 0 Vote négatif

dionys0s

XLDnaute Impliqué

• 27 Mars 2021

• #4

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

 

Vote positif 0 Vote négatif

C

Calvin

XLDnaute Nouveau

• 27 Mars 2021

• #5

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.

 

Vote positif 0 Vote négatif

eastwick

XLDnaute Accro

• 27 Mars 2021

• #6

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

 

Vote positif 0 Vote négatif

C

Calvin

XLDnaute Nouveau

• 27 Mars 2021

• #7

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.

 

Vote positif 0 Vote négatif

eastwick

XLDnaute Accro

• 27 Mars 2021

• #8

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...

 

Vote positif 0 Vote négatif

C

Calvin

XLDnaute Nouveau

• 27 Mars 2021

• #9

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

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 27 Mars 2021

• #10

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

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 27 Mars 2021

• #11

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

Vote positif 0 Vote négatif

Solution

C

Calvin

XLDnaute Nouveau

• 27 Mars 2021

• #12

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

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 27 Mars 2021

• #13

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

 

Vote positif 0 Vote négatif

C

Calvin

XLDnaute Nouveau

• 28 Mars 2021

• #14

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

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 28 Mars 2021

• #15

Calvin à dit:

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

Cliquez pour agrandir...

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

 

Vote positif 0 Vote négatif