Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to create a complex search function using userforms. The idea is to have a userform request for input, and then a search take place. If the number of found items is < 20, then the rows are copied to another sheet. If the found items exceed 20, then a second userform is populated, asking for more information to narrow the search, the results of which are used for a second search. Again, if the found items < 20, then the items are copied to another sheet, but if they exceed 20 then the process ends with no results. I have comments in the userforms that further explain this.

    I have set up the userforms and the worksheets in the attached, but would appreciate some assistance with the code that I need to make this happen. I am not certain that this is even possible, so any suggestions / input will be appreciated.

    This would be my expected search results based on the dummy data in the attached.

    404040 – 50505050 would return 9 items

    757575 – 76767676 would return 1 item

    202020 – 35353535 would find too many items so userform 2 would show. custref = “joint account” would also find too many items, therefore nothing would be returned.

    202020 – 35353535 would find too many items so userform 2 would show. custref = “from me” would return 4 items.

    [attachment=83814:search_2.xls]

    Many Thanks for any assistance.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Nathan
    Perhaps the following will get you started.
    Code:
    Sub PrimaryFilter(Crit8 As String, Crit9 As String)
     '
     
     Dim w As Worksheet
     Dim ctr As Long
     Dim Found As Long
     
    	 Set w = Worksheets("BC040-e")
    	 With w
    		 .AutoFilterMode = False
    		 .Rows("1:1").AutoFilter
    		 .Rows("1:1").AutoFilter Field:=8, Criteria1:=Crit8
    		 .Rows("1:1").AutoFilter Field:=9, Criteria1:=Crit9
    		 For ctr = 2 To 65536
    			 If Not .Rows(ctr).Hidden Then
    				 If Cells(ctr, 8) <> Crit8 Then Exit For
    				 Found = Found + 1
    			 End If
    		 Next ctr
    		 If Found < 20 Then
    			 Call Copy2OtherSht
    		 Else
    			 Call RefineFilter
    		 End If
    	 End With
     End Sub
     
     Public Sub Copy2OtherSht()
     
     End Sub
     
     Public Sub RefineFilter(Crit8 As String, Crit9 As String)
     
     End Sub
    Regards
    Don

  3. #3

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to Don, I have managed to get up and running with this, (kind of). My first UserForm is complete and works as I would expect. My problem now is as follows:
    If the search finds more than 20 items, then UserForm2 appears which requests further information. Userform 2 contains 3 text boxes, the first two I would like auto populated from UserForm1, How?? and then set focus on CustRef, How??.

    I think that this code is correct:

    SortCode2.Value = SortCode1
    AccountNumber2.Value = AccountNumber1
    CustRef.SetFocus

    But I do not know where to put it as UserForm2 will only ever be called from UserForm1.

    Please see new workbook attached.

    [attachment=83821:search_2.xls]

    Help Please.

    Added: And why does the "tab" go backwards on UF2??
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='775574' date='17-May-2009 11:38']Thanks to Don, I have managed to get up and running with this, (kind of). My first UserForm is complete and works as I would expect. My problem now is as follows:
    If the search finds more than 20 items, then UserForm2 appears which requests further information. Userform 2 contains 3 text boxes, the first two I would like auto populated from UserForm1, How?? and then set focus on CustRef, How??.

    I think that this code is correct:

    SortCode2.Value = SortCode1
    AccountNumber2.Value = AccountNumber1
    CustRef.SetFocus

    But I do not know where to put it as UserForm2 will only ever be called from UserForm1.

    Please see new workbook attached.

    [attachment=83821:search_2.xls]

    Help Please.

    Added: And why does the "tab" go backwards on UF2??[/quote]
    The attached will get you into the second form, and will restrict the user from revising previously input criteria. I have not yet worked out the details of where we go from here. It will require the user to identify the column on which the new criteria will operate.

    Sorry, but I cannot spend any more time on this today.
    Attached Files Attached Files
    Regards
    Don

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Don, Thanks very much, this gives me a starting point to play around with, I appreciate your time.

    .Rows("1:1").AutoFilter Field:=5, Criteria1:=CustRef

    The above will filter to = SortCode2 which is a variable. How do I change this to contains?

    Added later,

    Got it:

    .Rows("1:1").AutoFilter Field:=5, Criteria1:="=*" & CustRef & "*", Operator:=xlAnd

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Nathan

    I couldn't leave the darned thing alone. Try playing around with the attached.
    Attached Files Attached Files
    Regards
    Don

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    LOL: I have been working on this all day and have just about mastered it. I will take a look tomorrow when my eyes are a little fresher. Thanks very much for your time Don.

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='775617' date='17-May-2009 17:08'] LOL: I have been working on this all day and have just about mastered it. I will take a look tomorrow when my eyes are a little fresher. Thanks very much for your time Don.[/quote]

    Hi Nathan

    A further update:
    • When going to Form2, we allow the user to see the source data; and
    • we remove the results of prior searches before loading Form1
    Enjoy
    Attached Files Attached Files
    Regards
    Don

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Don,
    I really appreciate the time that you have spent on this. I have not had chance to have a proper look as yet as I have been dragged onto other things. Though I will come back to it soon, and learn lots...... Thanks again!

Posting Permissions

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