AW: Suchen in Datenbank
11.02.2023 10:43:52
Antonio
Hallo,
habe selber den Code bekommen (weiß nicht mehr von wem)
bei mir funzt super, habe eine Tabelle mit über 4000 Einträge, probiers aus ob für dich was sein konnte
Eingabe über eine ImputBox (klein Größ = wurscht)
Option Explicit
Sub SuchenUndKopieren()
Dim rng As Range, rngSearch As Range, rngFound As Range
Dim strFirst As String, strSearch As String
Dim lngRows() As Long, lngIndex As Long
Dim i As Integer
strSearch = InputBox("Bitte Suchbegriff eingeben:", "Suchen")
ReDim lngRows(0)
If strSearch > "" Then
With Sheets("Songliste") 'anpassen
If Selection.rows.Count = .rows.Count Then
Set rng = Selection
Else
Set rng = .UsedRange
End If
End With
Set rngSearch = rng.Find(what:=strSearch, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not rngSearch Is Nothing Then
strFirst = rngSearch.Address
Do
If IsError(Application.Match(rngSearch.row, lngRows, 0)) Then
ReDim Preserve lngRows(lngIndex)
lngRows(lngIndex) = rngSearch.row
lngIndex = lngIndex + 1
If rngFound Is Nothing Then
Set rngFound = rngSearch.EntireRow
Else
Set rngFound = Union(rngFound, rngSearch.EntireRow)
End If
End If
Set rngSearch = rng.FindNext(rngSearch)
Loop While Not rngSearch Is Nothing And strFirst > rngSearch.Address
End If
If Not rngFound Is Nothing Then
Sheets("Tabelle11").Range("A2:D14").ClearContents 'anpassen
rngFound.Copy Sheets("Tabelle1").Cells(2, 1) 'anpassen
End If
End If
Set rng = Nothing
Set rngSearch = Nothing
Set rngFound = Nothing
' Set objWB = Nothing
End Sub