recherche surtoutes bles feilles sauf la première

[michel.dupont]

bonjour
voici un code de recherche qui fonctionne correctement mais je voudrais que la recherche ne se fasse pas sur le premier onglet qui s'appelle "start";Pouvez-vous m'aider?
merci à tous
Michel

Sub Recherche()
Dim MaRecherche
Dim Ws As Worksheet
Dim c As Range
Dim Message As String, firstAddress As String
MaRecherche = Worksheets("start").Range("g31").Value
Message = "La valeur " & MaRecherche & " a été trouvée :" & Chr(10)
For Each Ws In Worksheets

With Ws
Set c = .Columns("A:Z").Find(What:=MaRecherche, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Message = Message & "- dans la feuille " & Ws.Name & ", cellule " & c.Address & Chr(10)
Set c = .Columns("A:Z").FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Next Ws


MsgBox Message
End Sub

 

[youky(BJ)]

Bonjour Michel,
A tester ceci,
Bruno

Edit: Désolé j'ai mal compris la demande le code n'est pas bon

Sub Recherche()
Dim nom
With Sheets("start")
.select
nom=.[G31]
Set c = .columns("A:Z").Find(nom, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Activate
rep = MsgBox("Continuer la recherche ?", 4 + 32, "Sélection")
If rep = vbNo Then Exit Sub
Set c = .columns("A:Z").FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
MsgBox "Recherche terminée!"
End Sub

 

[youky(BJ)]

Voici le corrigé tout simplement
j'ai ajouté le If Ws….et End if
Bruno

Sub Recherche()
Dim MaRecherche
Dim Ws As Worksheet
Dim c As Range
Dim Message As String, firstAddress As String
MaRecherche = Worksheets("start").Range("g31").Value
Message = "La valeur " & MaRecherche & " a été trouvée :" & Chr(10)
For Each Ws In Worksheets
if Ws.name<>"start" Then
With Ws
Set c = .Columns("A:Z").Find(What:=MaRecherche, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Message = Message & "- dans la feuille " & Ws.Name & ", cellule " & c.Address & Chr(10)
Set c = .Columns("A:Z").FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End If
Next Ws
MsgBox Message
End Sub

 

[michel.dupont]

merci Bruno 🙂