Sub Bouton1_Clic()
Dim quoi As Range, ou As Range, tablo, i&, nb&, res()
Static Indice&
Set quoi = Range("B2")
If IsEmpty(quoi) Then
' il n'y a rien à chercher
[B7:C7] = Empty
MsgBox "Cellule B2 vide", vbCritical
Indice = 0
Else
'Construction du tableau des correspondances
tablo = Range("$e$1:$f$6").Value
For i = 1 To UBound(tablo)
If StrComp(tablo(i, 2), quoi, vbTextCompare) = 0 Then
[B7] = tablo(i, 2)
nb = nb + 1
ReDim Preserve res(1 To nb)
res(nb) = tablo(i, 1)
End If
Next i
If nb = 0 Then
[B7:C7] = Empty
Indice = 0
MsgBox "Cellule B2 non trouvée", vbCritical
Else
'si l'ancien indice est possible et correspondait au critère de recherche actuel
If Indice > 0 And Indice <= nb Then If res(Indice) = [c7] Then Indice = Indice Else Indice = 0
Indice = Indice + 1
If Indice > nb Then Indice = 1
[c7] = res(Indice)
If Indice = nb Then Beep
End If
End If
quoi.Activate
End Sub