Discussion :

[Kestion100]

Bonjour,
Je cherche à ouvrir une inputbox pour rechercher un mot dans plusieurs feuilles d'un classeur.

Mon code ne me renvoie rien du tout après l'avoir trituré 1H.

Le code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Sub MaRECH()
'
' MaRECH Macro
'
 
'
Dim MaRecherche As String
 
MaRecherche = InputBox(MaRecherche, "Qui?")
 
 
 
    Dim i As Integer
 
    i = 6
    While i <= 64
 
    Worksheets(i).Activate
    Range("A1:A100").Select
 
    With Selection
 
 
 
    Set MotTrouvé = .Cells.Find(What:=MaRecherche, LookIn:=xlValues, LookAt:=xlPart) ', SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 
 
 
    If Not MotTrouvé Is Nothing Then
 
    MsgBox MoTrouvé
 
        MotTrouvé.Select
 
        Exit Sub
 
        End If
 
 
    End With
 
    i = i + 1
 
    Wend
End Sub

L'objet de la recherche est un mot issu d'une formule.

Si quelqu'un peut me débloquer, MERIC!!!!!!!!!!!!!!!!!

[Kestion100]

Avec le Code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub MaRECH()
'
' MaRECH Macro
'
 
'
Dim MaRecherche As String 
Dim ws As Worksheet
Dim c As Range
Dim Message As String, firstAddress As String, DEUXMessage As String
 
MaRecherche = InputBox(MaRecherche, "Qui?")
 
 
 
 
    For Each ws In Worksheets
        With ws
 
            Set c = .Columns("A:XFD").Find(What:=MaRecherche, LookIn:=xlValues, LookAt:=xlPart)
            If Not c Is Nothing Then
 
            Sheets(ws.Name).Activate
            Range(c.Address).Select
 
            firstAddress = c.Address
          c.Address & Chr(10)
 
 
                    Set c = .Columns("A:XFD").FindNext(c)
 
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
 
            End With
            Next
End Sub

C'est bon.

Un petit point quand même, j'aimerais limiter ma recherche sur n feuilles et non pas toute, comment dois-je procéder?

[Kestion100]

Trouvé:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Sub MaRECH()
'
' MaRECH Macro
'
 
'
Dim MaRecherche As String 
Dim ws As Worksheet
Dim c As Range
Dim firstAddress As String
 
MaRecherche = InputBox(MaRecherche, "Qui?")
 
 
 
 
    For Each ws In Worksheets(Array("0910776Z", "0951801S", "0780050F", "0780187E", "0781105C", "0781898P", _
        "0781951X", "0781974X", "0782540M", "0782546U", "0782557F", "0783053V", "0783282U", _
        "0783286Y", "0783307W", "0910622G", "0910625K", "0910727W", "0910975R", "0911403F", _
        "0911492C", "0912000E", "0912163G", "0912364A", "0920130S", "0920131T", "0920145H", "0920146J", "0920897A", "0921365J", "0921631Y", _
        "0921778H", "0922149L", "0922247T", "0922340U", "0922615T", "0950649P", "0950650R", _
        "0950722U", "0951090U", "0951190C", "0951194G", "0951399E", "0951637N", "0951673C", _
        "0951697D", "0951722F", "0951726K", "0951727L", "0951756T"))
        With ws
 
            Set c = .Columns("A:XFD").Find(What:=MaRecherche, LookIn:=xlValues, LookAt:=xlPart)
            If Not c Is Nothing Then
 
            Sheets(ws.Name).Activate
            Range(c.Address).Select
 
            firstAddress = c.Address
 
                Do
 
 
                    Set c = .Columns("A:XFD").FindNext(c)
 
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
 
            End With
            Next
End Sub