AW: Zelle suchen
31.12.2012 10:16:32
Hajo_Zi
Sub Find_mehrmals()
'* H. Ziplies *
'* 04.05.10 *
'* erstellt von HajoZiplies@WEB.de *
'* http://Hajo-Excel.de
Dim RaFound As Range
Dim FirstAddress As String
Dim Search As String
Dim LoLetzte As Long
Dim LoI As Long
Dim ByMldg As Byte
Search = Worksheets("Tabelle2").Range("A1")
With Worksheets("Tabelle1")
LoLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, . _
Rows.Count)
' von Peter Haserodt
Set RaFound = .Range("A1:A" & LoLetzte).Find(Search, .Range("A" & LoLetzte), , _
xlWhole, , xlNext)
If RaFound Is Nothing Then Exit Sub 'falls nicht gefunden wird sub verlassen
If MsgBox("Gefunden in Zelle " & RaFound.Address(0, 0) & " ist dies richt richtige _
Zelle !!!" _
, vbYesNo + vbQuestion, "Abfrage") = 6 Then
RaFound.Interior.ColorIndex = 3
Else
FirstAddress = RaFound.Address
Do
Set RaFound = .Range("A1:A" & LoLetzte).FindNext(RaFound)
If RaFound.Address = FirstAddress Then Exit Sub
If MsgBox("Gefunden in Zelle " & RaFound.Address(0, 0) & " ist dies richt _
richtige Zelle !!!" _
, vbYesNo + vbQuestion, "Abfrage") = 6 Then
RaFound.Interior.ColorIndex = 3
Exit Do
End If
If RaFound.Row = LoLetzte Then Exit Do
LoI = LoI + 1
Loop
End If
End With
Set RaFound = Nothing
End Sub