+ Reply to Thread
Results 1 to 3 of 3

How to search 2 criteria using this search function?

Hybrid View

puuts How to search 2 criteria... 05-16-2014, 03:53 PM
playaller Re: How to search 2 criteria... 05-16-2014, 04:18 PM
puuts Re: How to search 2 criteria... 05-16-2014, 04:25 PM
  1. #1
    Forum Contributor
    Join Date
    02-26-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    367

    How to search 2 criteria using this search function?

    Hello Gurus,

    I have an 'Update' Module that search the value of the Userform1.txt_sc.Value in Column B, but I want to search to 2 criteria, how can i amend the below code to search for 2 criteria ( userform1.txt_sc.value & userform1.txt_linenum.value )

    
    Dim strSrch As String
    Dim rSrch As Range
    Dim Search As Integer
    Dim X As Range
    
    Set X = Sheets("Sheet1").Range("B:B").Find(what:=UserForm1.txt_sc.Value, LookIn:=xlValues, _
                        lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
    
    If Not X Is Nothing Then
        With Me
          .cb_region.Value = X.Offset(0, 4).Value
          .txt_requestor.Value = X.Offset(0, -1).Value
          .cb_clean.Value = X.Offset(0, 5).Value
          .txt_received.Value = X.Offset(0, 1).Value
          .txt_complete.Value = X.Offset(0, 2).Value
          .txt_pm.Value = X.Offset(0, 16).Value
          .txt_rmgr.Value = X.Offset(0, 17).Value
          .txt_wbse.Value = X.Offset(0, 6).Value
          .txt_so.Value = X.Offset(0, 8).Value
          .txt_partnername.Value = X.Offset(0, 9).Value
          .txt_partnernumber.Value = X.Offset(0, 10).Value
          .txt_linenum.Value = X.Offset(0, 7).Value
          .txt_lineamount.Value = X.Offset(0, 14).Value
          .txt_currency.Value = X.Offset(0, 20).Value
          .cb_type.Value = X.Offset(0, 21).Value
          .txt_rname.Value = X.Offset(0, 15).Value
          .cb_status.Value = X.Offset(0, 18).Value
          .txt_po.Value = X.Offset(0, 13).Value
          .txt_podate.Value = X.Offset(0, 12).Value
          .txt_remarks.Value = X.Offset(0, 19).Value
          
    End With
            Else
               
    End If

  2. #2
    Valued Forum Contributor
    Join Date
    09-01-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    324

    Re: How to search 2 criteria using this search function?

    I haven't tested but you can try this. You didn't say if both were required so this assumes both are.
    If you need to paste something offset of the second search then you need to add the .value = X2.Offset(#,#).Value's in with the ones you already have below.


    Dim strSrch As String
    Dim rSrch As Range
    Dim Search As Integer
    Dim X As Range
    Dim X2 As Range
    
    'Search Criteria 1
    Set X = Sheets("Sheet1").Range("B:B").Find(what:=Userform1.txt_sc.Value, LookIn:=xlValues, _
                        lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
    
    'Search Criteria 2
    Set X2 = Sheets("Sheet1").Range("B:B").Find(what:=Userform1.txt_linenum.Value, LookIn:=xlValues, _
                        lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
    
    If X > 0 And X2 > 0 Then
    
        With Me
          .cb_region.Value = X.Offset(0, 4).Value
          .txt_requestor.Value = X.Offset(0, -1).Value
          .cb_clean.Value = X.Offset(0, 5).Value
          .txt_received.Value = X.Offset(0, 1).Value
          .txt_complete.Value = X.Offset(0, 2).Value
          .txt_pm.Value = X.Offset(0, 16).Value
          .txt_rmgr.Value = X.Offset(0, 17).Value
          .txt_wbse.Value = X.Offset(0, 6).Value
          .txt_so.Value = X.Offset(0, 8).Value
          .txt_partnername.Value = X.Offset(0, 9).Value
          .txt_partnernumber.Value = X.Offset(0, 10).Value
          .txt_linenum.Value = X.Offset(0, 7).Value
          .txt_lineamount.Value = X.Offset(0, 14).Value
          .txt_currency.Value = X.Offset(0, 20).Value
          .cb_type.Value = X.Offset(0, 21).Value
          .txt_rname.Value = X.Offset(0, 15).Value
          .cb_status.Value = X.Offset(0, 18).Value
          .txt_po.Value = X.Offset(0, 13).Value
          .txt_podate.Value = X.Offset(0, 12).Value
          .txt_remarks.Value = X.Offset(0, 19).Value
       End With
    
    Else
               
    End If
    Last edited by playaller; 05-16-2014 at 04:23 PM. Reason: Updated End if Line


    Shelton A.
    If Helpful, Add Reputaion!

  3. #3
    Forum Contributor
    Join Date
    02-26-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    367

    Re: How to search 2 criteria using this search function?

    Will give this a try Shelton. Will keep you posted mate!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-18-2012, 05:52 AM
  2. Search all worksheets and paste all rows w/search criteria to single sheet
    By hutch@edge.net in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-22-2012, 01:31 PM
  3. Search Multiple Worksheets Against List of Non-Exact Search Criteria?
    By thump4r in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2010, 03:46 PM
  4. Advance Search and GoTo a cell that meets a certain search criteria
    By moatik in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-21-2010, 04:01 PM
  5. How do I search excel spreadsheets using multiple search criteria.
    By Kasper in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-14-2005, 08:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1