Search/Filter a Spreadsheet by User Entered Value

[jbennett01]

I am trying to create a search feature in a spreadsheet that will allow the user to enter search terms and have the macro find all the matching entries. Some of the search terms are specific, known quantities such as customer name, line of business abbreviation. Others are more free form because the data in those fields depends on the topic. The code is below and the Filter by Issue section is where I am having trouble. Any suggestions would be greatly appreciated.

Sub Search_Feature()

    ' Macro to search changement management directives.
    
        ' Declare variables
    Dim strLOB, strArea, strIssue As String
    Dim lngTopFilteredRow, lngLastFilteredRow, lngCurrentRow, lngLastRow, lngSynopsis As Long
    Dim intClientCount, intClientLen As Integer
   
                ' Capture search term(s)
    strLOB = Range("B2").Value
    strArea = Range("B3").Value
    strIssue = Range("B4").Value
    lngSynopsis = Range("B5").Value
      
                ' Get last row of Directives tab
    Worksheets(2).Activate
    lngLastRow = LastRowSearch("A")
    If Range("A" & lngLastRow).Value = "" Then lngLastRow = lngLastRow - 1
    
                ' Filter by LOB, if applicable
    If strLOB <> "" Then
        ActiveSheet.Range("$A$1:$H$" & lngLastRow).AutoFilter Field:=4, Criteria1:=strLOB, Operator:=xlFilterValues
    End If
    
                ' Filter by Area, if applicable
    If strArea <> "" Then
        ActiveSheet.Range("$A$1:$H$" & lngLastRow).AutoFilter Field:=5, Criteria1:=strArea, Operator:=xlFilterValues
    End If
    
                ' Filter by Issue, if applicable
    If strIssue <> "" Then
        ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=6, Criteria1:=Array(strIssue), Operator:=xlFilterValues
    End If
    
                ' Determine top and bottom filtered rows
    lngTopFilteredRow = TopFilteredRowSearch("A")
    lngLastFilteredRow = LastFilteredRowSearch("A")
            
                ' Remove all filtering and move to top of form.
    Range("A2").Activate
    ActiveSheet.ShowAllData
    
                ' Copy search results to Search Tab
    Range("A" & lngTopFilteredRow & ":H" & lngLastFilteredRow).Select
    Selection.Copy
    Sheets("Search Tab").Select
    Worksheets(1).Activate
    Range("A9").Select
    ActiveSheet.Paste
    
End Sub

 

[myall_blues]

It would be helpful if you were able to post some sample data for us to work with, preferably using XL2BB so that we don't have to reenter.

In the meantime, your variable declarations in your code are not doing what you think they are. VBA requires every variable to be explicitly declared, or it defaults to variant (which will generally work but may have unexpected results).

So for example in your line

Dim strLOB, strArea, strIssue As String

only strIssue is declared as String. The other two are variant.
If you want them all to be string then you need to put

Dim strLOB as String, strArea as String, strIssue As String

 

[jbennett01]

Thank you. I did not know that. I'll have to fix a whole lot of macros. However, I cannot easily provide sample data as it is proprietary and confidential. Let me try to clarify, first. If that does not work, I will see about sample data. Below is the place where the search items are entered. LOB and Area have only specific options. For example, the LOB can only be, say, Marketing, Repair, etc. Area is the same but with more options. However both Issue and Synopsis are free-form text fields, meaning anything can be typed into that field. I want the macro to filter the Issue or Synopsis field by whatever is entered into those search fields. In the example below, there are several entries in the spreadsheet where the word "Agree" is used. I want this search to return all the entries in the LOB 'Marketing' that have the word "Agree" (or whatever) is entered into the Issue field. How do I do that?

 

[jbennett01]

I figured it out, or at least a way to make it work. Turned out to be very simple. Just needed to put wildcard around the variable in the Criteria1 element.

            ' Filter by Issue, if applicable
    If strIssue <> "" Then
        ActiveSheet.ListObjects("Directives").Range.AutoFilter Field:=6, Criteria1:="*" & strIssue & "*", Operator:=xlFilterValues
    End If
    
                ' Filter by Synopsis, if applicable
    If strSynopsis <> "" Then
        ActiveSheet.ListObjects("Directives").Range.AutoFilter Field:=8, Criteria1:="*" & strSynopsis & "*", Operator:=xlFilterValues
    End If