Table filter search box VBA not working...

[duteberta]

I've been working all day to no avail. Not sure why table is not filtering when activating this macro.

Option Explicit

Sub AgentFilter()

    Dim aQuery As String
    Dim LastRow As Long
    
    With sh_AGENTS
        
            LastRow = .Range("C999999999").End(xlUp).Row
            If LastRow < 6 Then LastRow = 6
            
            If .Range("E3") = "Enter search criteria here…" Then aQuery = Empty
            Else: aQuery = .Range("E3").Value
            
            .Range("C7:T" & LastRow).Select
            
            Selection.AutoFilter
                
            With .Range("tbl_AGENTS")
                
                If aQuery <> Empty Then .AutoFilter Field:=2, Criteria:="=*" & Query & "*"
'                If aQuery <> Empty Then .AutoFilter Field:=5, Criteria:="=*" & Query & "*"
'                If aQuery <> Empty Then .AutoFilter Field:=6, Criteria:="=*" & Query & "*"
'                If aQuery <> Empty Then .AutoFilter Field:=7, Criteria:="=*" & Query & "*"

            End With
            
            
    End With

End Sub

Sub ClearAgentFilter()

        With sh_AGENTS
        
'            .Range("A6").Value = True
            
                .AutoFilterMode = False
                .Range("E3").Value = "Enter search criteria here…"
                
'            .Range("A6").Value = False
        
        End With
        
End Sub

My list of agents is very long (about 10,000 agents) I only want to search for matches in columns 2,5,6,7 corresponding to Columns: [MLSID], [Last], [OfficeID], [OfficeName].

 

[ranman256]

asterisk wildcards need LIKE, (not = )
tho not sure if it works in excel that way:

Criteria:=" like *" & Query & "*"

 

[Akuini]

If aQuery <> Empty Then .AutoFilter Field:=2, Criteria:="=*" & Query & "*"

Is it aQuery or Query?

 

[duteberta]

Oh! It's "aQuery" I fixed it but it still doesn't work. Thanks for catching.

 

[Akuini]

Could you explain in more detail what you're trying to do?
Are you trying to use a keyword to filter 4 columns?
Why not using one keyword for each column? So, basically you have 4 cell to input the keyword, each cell will be used to search in one column.

 

[duteberta]

Could you explain in more detail what you're trying to do?
Are you trying to use a keyword to filter 4 columns?
Why not using one keyword for each column? So, basically you have 4 cell to input the keyword, each cell will be used to search in one column.

I'd like to enter any text or number in the search bar (Cell E3) and as I type I'd like the table records below it to filter in real time with each keystroke. I don't want a "search" button if possible. Only a CLEAR button that resets the table showing all values and adding the text in the search bar "Enter search criteria here…"

The columns that need to be searched are columns 2,5,6,7 of the table called "tbl_AGENTS" which corresponds to Columns: [MLSID], [Last], [OfficeID], [OfficeName].

The wildcards should be on both sides of the search value. "*" & SearchValue & "*"

Once I get the correct VBA syntax for this table- I will adapt it to my other long tables.

 

[Akuini]

to filter in real time with each keystroke

You need to input the keyword in an activex textbox instead of a cell. However, you're using Mac Excel, is activex control work on Mac?

 

[duteberta]

Yes I use a Mac

 

[Akuini]

Do activex controls work on Mac Excel?

 

[duteberta]

no they do not