Saturday, August 13, 2011

Search 3 Keywords Once

Hi,

Recently I'm working for someone , he asked me to search 3 keywords once.
here is the codes:

userform













Private Sub CommandButton1_Click()
    Dim Kw, Xkw, Xsrc, Wkn As Worksheet, i, X, Wk1 As Worksheet, Txt As Variant
   
    If MsgBox("Do you want to copy results to a new sheet ? ", vbYesNo) = vbNo Then End
 
    Set Wk1 = Sheet1
    Set Wkn = Worksheets.Add(, Sheet1)
 
    Wk1.Activate
    Txt = Split(Replace(TextBox3.Text, Chr(32), ""), Chr(44))
 

    For X = 0 To UBound(Txt)
        For i = 2 To Wk1.[A65536].End(xlUp).Row
            Set c = Wkn.Cells.Find(Wk1.Cells(i, 1).Value, LookIn:=xlValues)
            If c Is Nothing Then
                If (UBound(Split(Wk1.Cells(i, 1), Chr(32))) + 1 > Val(TextBox1.Value)) And _
 _
                (Val(Wk1.Cells(i, 3)) > Val(TextBox2.Value)) And _
                Val(InStr(1, Wk1.Cells(i, 1), Txt(X))) > Val(0) Then _
 _
                Wk1.Range("A" & i & ":C" & i).Copy _
                Wkn.Range("A" & Wkn.Cells(65536, 1).End(xlUp).Row + 1)
            End If
        Next i
    Next X
 
    Wkn.Columns("a:c").EntireColumn.AutoFit
 
    'Wkn.Name = TextBox3.Text & ThisWorkbook.Sheets.Count
    Sheet1.[f1] = TextBox1.Value
    Sheet1.[f2] = TextBox2.Value
 
    End
   
End Sub

Private Sub CommandButton2_Click()
    End
End Sub


Private Sub TextBox1_Change()
    Sheet1.[f1] = TextBox1.Value
End Sub
Private Sub TextBox2_Change()
    Sheet1.[f2] = TextBox2.Value
 
End Sub

Private Sub UserForm_Initialize()
    TextBox1 = Sheet1.[f1]
    TextBox2 = Sheet1.[f2]
    TextBox3 = "forex, trading, peter"
End Sub


He said satisfied with this result.








No comments:

Post a Comment