Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Unanswered: Add "Next Result" button into Search Function

    Hey everybody,

    First time poster so please be gentle. Up until a few weeks ago I had never done any programming in VBA or any other language, but on been handed a project at work I needed to learn quite rapidly.

    Basically I have a multipage user form that allows the user to perform search function on various types of data and then displays the corresponding results in text boxes I.E. search on a team member and it will display their details, ext number hours of work etc.

    Unfortunately I've hit a brick wall with my coding. One lot of data consists of 4000+ financial adviser details - and I've written some code which will allow the user to search by company name, name of financial adviser or a unique identifying number (that the user may not know).

    The problem is when the user searches under company name or fa name there is the possibility of multiple results. As an example if the user searches under the company Insight Financial, there are 6 occurances each with a different FA name, contact details unique identifier etc. The code I have only brings up the very first result I have created a command button labled cbfindnext and try as I might I cannot figure out the code to make the search skip to the next occurance of the search term and display the new results when clicked.

    Below is the code I have used for my search that brings up the first result only:
    Code:
    Sub FindMe()
        Dim FindThis As String
        Dim LastColumn, LastRow As Integer
        Dim rw, FirstAddress, LastCell, SrchRnge
          
        'Search by Company name
        If UserForm1.acccompnamesearch.Value <> "Enter Company Name" Then
           
        LastRow = Sheet5.Range("$C$3:$C$4621").End(xlDown).Row
        LastColumn = Sheet5.Range("$A$3").End(xlToRight).Column
        LastCell = Cells(LastRow, LastColumn).Address
        SrchRnge = "$C$3:$C$4621" '& LastCell
         
        FindThis = UserForm1.acccompnamesearch
         
        With Sheet5.Range(SrchRnge).Cells
            Set rw = .Find(What:=FindThis, After:=Sheet5.Range("$C$3"), LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        End With
         
        On Error GoTo notfound
         
        UserForm1.accagencynum = Sheet5.Range(rw.Address).Offset(0, -2)
        UserForm1.accumbrella = Sheet5.Range(rw.Address).Offset(0, 1)
        UserForm1.acccompname1 = Sheet5.Range(rw.Address).Offset(0, 0)
        UserForm1.accfaname = Sheet5.Range(rw.Address).Offset(0, 2)
        UserForm1.acccompaddress = Sheet5.Range(rw.Address).Offset(0, 3)
        UserForm1.accofficenum = Sheet5.Range(rw.Address).Offset(0, 4)
        UserForm1.accofficefax = Sheet5.Range(rw.Address).Offset(0, 5)
        UserForm1.accconsultant = Sheet5.Range(rw.Address).Offset(0, 6)
        UserForm1.accracfid = Sheet5.Range(rw.Address).Offset(0, 7)
        UserForm1.acctelnum = Sheet5.Range(rw.Address).Offset(0, 8)
        UserForm1.accteamleader = Sheet5.Range(rw.Address).Offset(0, 9)
    
         
    notfound:
        If UserForm1.acccompname1 = "" Then
            MsgBox (FindThis & " is not a valid Company.")
        Else
        End If
        'Searches by Unique Agency number 
        ElseIf UserForm1.accagencynumsearch.Value <> "Enter Agency Number" Then
        
        LastRow = Sheet5.Range("$A$3:$A$4621").End(xlDown).Row
        LastColumn = Sheet5.Range("$A$3").End(xlToRight).Column
        LastCell = Cells(LastRow, LastColumn).Address
        SrchRnge = "$A$4:$A$4621" '& LastCell
         
        FindThis = UserForm1.accagencynumsearch
         
        With Sheet5.Range(SrchRnge).Cells
            Set rw = .Find(What:=FindThis, After:=Sheet5.Range("$A$4"), LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        End With
         
        On Error GoTo notfound1
         
        UserForm1.accagencynum = Sheet5.Range(rw.Address).Offset(0, 0)
        UserForm1.accumbrella = Sheet5.Range(rw.Address).Offset(0, 3)
        UserForm1.accfaname = Sheet5.Range(rw.Address).Offset(0, 4)
        UserForm1.acccompaddress = Sheet5.Range(rw.Address).Offset(0, 5)
        UserForm1.accofficenum = Sheet5.Range(rw.Address).Offset(0, 6)
        UserForm1.accofficefax = Sheet5.Range(rw.Address).Offset(0, 7)
        UserForm1.acccompname1 = Sheet5.Range(rw.Address).Offset(0, 2)
        UserForm1.accconsultant = Sheet5.Range(rw.Address).Offset(0, 8)
        UserForm1.accracfid = Sheet5.Range(rw.Address).Offset(0, 9)
        UserForm1.acctelnum = Sheet5.Range(rw.Address).Offset(0, 10)
        UserForm1.accteamleader = Sheet5.Range(rw.Address).Offset(0, 11)
    
    notfound1:
        If UserForm1.accagencynum = "" Then
            MsgBox (FindThis & " is not a valid Agency Number.")
        Else
        End If
        'searches by Financial adviser name
      Else
        LastRow = Sheet5.Range("$E$3:$E$4621").End(xlDown).Row
        LastColumn = Sheet5.Range("$E$3").End(xlToRight).Column
        LastCell = Cells(LastRow, LastColumn).Address
        SrchRnge = "$E$3:$E$4621" '& LastCell
         
        FindThis = UserForm1.accfanamesearch
         
        With Sheet5.Range(SrchRnge).Cells
            Set rw = .Find(What:=FindThis, After:=Sheet5.Range("$E$3"), LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        End With
         
        On Error GoTo notfound2
         
        UserForm1.accagencynum = Sheet5.Range(rw.Address).Offset(0, -4)
        UserForm1.accumbrella = Sheet5.Range(rw.Address).Offset(0, -1)
        UserForm1.acccompname1 = Sheet5.Range(rw.Address).Offset(0, -2)
        UserForm1.accfaname = Sheet5.Range(rw.Address).Offset(0, 0)
        UserForm1.acccompaddress = Sheet5.Range(rw.Address).Offset(0, 1)
        UserForm1.accofficenum = Sheet5.Range(rw.Address).Offset(0, 2)
        UserForm1.accofficefax = Sheet5.Range(rw.Address).Offset(0, 3)
        UserForm1.accconsultant = Sheet5.Range(rw.Address).Offset(0, 4)
        UserForm1.accracfid = Sheet5.Range(rw.Address).Offset(0, 5)
        UserForm1.acctelnum = Sheet5.Range(rw.Address).Offset(0, 6)
        UserForm1.accteamleader = Sheet5.Range(rw.Address).Offset(0, 7)
         
    notfound2:
        If UserForm1.acccompname1 = "" Then
            MsgBox (FindThis & " is not a valid FA.")
        Else
        End If
     End If
    End Sub
    Is there any kind soul out there who could help with the coding of the "Find Next" button and how to integrate it into the above search? I am now way out of my depth with this and really struggling.

    On another note I've managed to add a minimise button to my userform using the following code:
    Code:
    Private Declare Function FindWindowA Lib "USER32" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
    
    Private Declare Function GetWindowLongA Lib "USER32" _
    (ByVal hWnd As Long, _
    ByVal nIndex As Long) As Long
    
    Private Declare Function SetWindowLongA Lib "USER32" _
    (ByVal hWnd As Long, _
    ByVal nIndex As Long, _
    ByVal dwNewLong As Long) As Long
    
    Option Explicit
    
    Sub FormatUserForm(UserFormCaption As String)
    
    Dim hWnd            As Long
    Dim exLong          As Long
    
        hWnd = FindWindowA(vbNullString, UserFormCaption)
        exLong = GetWindowLongA(hWnd, -16)
        If (exLong And &H20000) = 0 Then
            SetWindowLongA hWnd, -16, exLong Or &H20000
            Else
        End If
    
    End Sub
    Which minimises it nicely into the corner above the start button. Can anyone help with code to minimise it into the actual task bar?

    My apologies for the really long post. I was just trying to explain things as much as possible.
    Thanks iin advance for any help.

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Attached is an Excel 2003 example of finding First, then Next. I've done this in
    a worksheet and use a global variable declared in Module1 to store the target
    text for doing a 'Next' search. You could store this value in your form's 'Find'
    button tag property, then refer to it when the 'Next' button is clicked. For your
    convenience, the workbook is attached.
    Code:
    Private Sub CommandButton1_Click()
    Dim rngFound As Range
        
        strFindTarget = InputBox("Enter your target")
        
        If strFindTarget = "" Then Exit Sub
        Range("A1").Select
        
        On Error Resume Next
        Set rngFound = Cells.Find(What:=strFindTarget, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        On Error GoTo 0
            
        If rngFound Is Nothing Then
            MsgBox "Target not found: " & strFindTarget
            Exit Sub
        Else
            rngFound.Activate
            MsgBox "First instance of the target found at row " & rngFound.Row
        End If
        
    End Sub
    
    Private Sub CommandButton2_Click()
    'find next target that contains text in global variable strFindTarget
    Dim rngFound As Range
    Dim prevRow As Long
        
        prevRow = ActiveCell.Row
        
        If Trim(strFindTarget) = "" Then
            MsgBox "Use Find function before using Next function"
            Exit Sub
        End If
        
        On Error Resume Next
        Set rngFound = Cells.Find(What:=strFindTarget, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        On Error GoTo 0
         
        If Not rngFound Is Nothing Then
            rngFound.Activate
            
            If prevRow >= rngFound.Row Then
                Cells(prevRow, 1).Select
                MsgBox "No more instances of target " & strFindTarget
            Else
                MsgBox "Next target found at row " & rngFound.Row
            End If
        End If
            
    End Sub
    Attached Files Attached Files

Posting Permissions

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