Re: Suchen in Bereich
20.02.2003 09:40:51
Bernd Held
Hallo Gusti,so was in der Art sollte für die Suche funktionieren:
Private Sub CommandButton7_Click()
'Kundensuche durchführen
Dim l As Long
Dim i As Integer
Dim s As String
Dim s_Dat As String
Dim s_Vor As String
If UserForm1.TextBox4.Value = "" And UserForm1.TextBox5.Value = "" Then GoTo Ende
s_Dat = ThisWorkbook.Path
ChDir s_Dat
Application.ScreenUpdating = False
Workbooks.Open s_Dat & "\Kundenstamm.xls"
Sheets("Kundenstamm").Activate
With UserForm1
.ListBox2.Clear
End With
'Suchbegriff aus Textbox4 und Textbox5 ableiten
s = LCase(UserForm1.TextBox4.Value)
s_Vor = LCase(UserForm1.TextBox5.Value)
Range("B2").Select
i = 0
'Suche eines Kunden im Kundenstamm
For l = 3 To ActiveSheet.UsedRange.Rows.Count + 1
If InStr(LCase(ActiveCell.Value), s) > 0 And _
InStr(LCase(ActiveCell.Offset(0, 1).Value), LCase(s_Vor)) <> 0 Then
With UserForm1
'Listbox2 mit gefundenen Kunden füllen
.ListBox2.AddItem ActiveCell.Value
.ListBox2.Column(1, i) = ActiveCell.Value
.ListBox2.Column(1, i) = ActiveCell.Offset(0, 1).Value
.ListBox2.Column(2, i) = ActiveCell.Offset(0, 2).Value
.ListBox2.Column(3, i) = ActiveCell.Offset(0, 3).Value
.ListBox2.Column(4, i) = ActiveCell.Offset(0, 4).Value
.ListBox2.Column(5, i) = ActiveCell.Offset(0, 5).Value
.ListBox2.Column(6, i) = ActiveCell.Offset(0, 6).Value
.ListBox2.Column(7, i) = ActiveCell.Offset(0, 7).Value
.ListBox2.Column(8, i) = ActiveCell.Offset(0, 8).Value
.ListBox2.Column(9, i) = ActiveCell.Row
End With
i = i + 1
Else
End If
ActiveCell.Offset(1, 0).Select
Next l
Application.ScreenUpdating = True
ActiveWorkbook.Close savechanges:=True
Exit Sub
Ende:
End Sub
Viele Grüße
Bernd
MVP für Microsoft Excel
Excel-Tipps, VBA in 21 Tagen mit Leseprobe(PDF) unter: http://held-office.de