Range nach bestimmten Wert suchen und diesen lösch
19.01.2014 19:25:43
Hajo_Zi
Hallo Ben,
benutze Find.
Sub Find_mehrmals()
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 Not RaFound Is Nothing Then
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 If
End With
Set RaFound = Nothing
End Sub