Thema: Wörter eines Satzes in Indizliste suchen

StandardModule: basMain

Sub CallForm()
   frmSearch.Show
End Sub

ClassModule: frmSearch

Private Sub cmdCancel_Click()
   Unload Me
End Sub

Private Sub cmdSearch_Click()
   Dim rng As Range
   Dim var As Variant
   Dim arr() As Integer
   Dim iRow As Integer, iCounter As Integer
   Dim sWord As String, sTxt As String
   Dim bln As Boolean
   sTxt = txtSearch.Text
   If Right(sTxt, 1) = "?" Or Right(sTxt, 1) = "." Then _
      sTxt = Left(sTxt, Len(sTxt) - 1)
   ReDim arr(1 To 1)
   Worksheets("Data").Rows("1:" & Rows.Count).Hidden = False
   Do Until sTxt = ""
      If InStr(sTxt, " ") Then
         sWord = Left(sTxt, InStr(sTxt, " ") - 1)
         sTxt = Right(sTxt, Len(sTxt) - InStr(sTxt, " "))
      Else
         sWord = sTxt
      End If
      iRow = 2
      With Worksheets("Data")
         Do Until IsEmpty(.Cells(iRow, 1))
            Set rng = .Range(.Cells(iRow, 3), _
               .Cells(iRow, 256)) _
               .Find(sWord, lookat:=xlPart, _
               LookIn:=xlValues)
            If Not rng Is Nothing Then
               var = Application.Match(iRow, arr, 0)
               If IsError(var) Then
                  iCounter = iCounter + 1
                  ReDim Preserve arr(1 To iCounter)
                  arr(iCounter) = iRow
               End If
            End If
            iRow = iRow + 1
         Loop
      End With
      If InStr(sTxt, " ") = False Then
         If bln Then Exit Do Else bln = True
      End If
   Loop
   Worksheets("Data").Rows("2:" & Rows.Count).Hidden = True
   For iRow = 1 To iCounter
      Worksheets("Data").Rows(arr(iRow)).Hidden = False
   Next iRow
   Worksheets("Data").Select
   Unload Me
End Sub

Beiträge aus dem Excel-Forum zu den Themen Dialog und TextBox