Gruppe
Dialog
Problem
Jedes Wort einer Supportfrage soll in einer Indizliste gesucht und es soll die entsprechende Antwort angezeigt werden.
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