AW: Suchen mit 3Suchkrieterien und Wenn Funktion
22.01.2007 14:26:01
Georg
Sub drei_kriterien_suche()
Dim Text, t1, t2, t3, t4, tmp As String, x, z As Integer
Text = "tu fa me" 'Suchtext (3 Kriterien)
tmp = InStr(1, Text, " ")
If tmp > 0 And InStr(tmp + 1, Text, " ") = 0 Then
t1 = Mid(Text, 1, tmp - 1)
t2 = Mid(Text, tmp + 1, Len(Text))
For x = 1 To Cells(Rows.count, 1).End(xlUp).row 'Durchsucht bis letzter Eintrag in Spalte A
If InStrRev(Range("A" & x).Value, t1, , vbTextCompare) > 0 And InStrRev(Range("A" & x).Value, t2, , vbTextCompare) > 0 Then MsgBox Range("A" & x).Value
Next x
ElseIf tmp > 0 And InStr(tmp + 1, Text, " ") > 0 Then
t1 = Mid(Text, 1, tmp - 1)
t2 = Mid(Text, tmp + 1, InStr(tmp + 1, Text, " ") - (tmp + 1))
t3 = Mid(Text, InStr(tmp + 1, Text, " ") + 1, Len(Text))
For x = 1 To Cells(Rows.count, 1).End(xlUp).row
If InStrRev(Range("A" & x).Value, t1, , vbTextCompare) > 0 And InStrRev(Range("A" & x - 2).Value, t2, , vbTextCompare) > 0 And InStrRev(Range("A" & x - 2).Value, t3, , vbTextCompare) > 0 Then MsgBox Range("A" & x).Value
Next x
Else
t1 = Text
For x = 1 To Cells(Rows.count, 1).End(xlUp).row
If InStrRev(Range("A" & x).Value, t1, , vbTextCompare) > 0 Then MsgBox Range("A" & x).Value
Next x
End If
End Sub
Durch sucht nach 3 Kriterien "tu fa me" und findet z.B. "Fahrrad Turnier Meisterschaften"
lg, Georg