Need some help with search function

[smichael]

I'm working with an inventory database and would like a search function to be similar to the "filter" function. I have code (found on another topic) that will do the search, but selects each cell one at a time. I would ideally like the code to filter out all of the cells, and only display the responses to the search. It would be great if somebody could tell me how to do this.

Thanks!

Here is the code:

Public Sub FindText1()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range
Dim rngNm As String, myText As String, FirstAddress As String
Dim AddressStr As String, thisLoc As String
Dim foundNum As Long
Dim myF As Variant, myRD As Variant
myAgain:
myText = ""
FirstAddress = ""
foundNum = 0
rngNm = ""
AddressStr = ""
thisLoc = ""
myF = ""
myRD = ""
myText = InputBox("Enter text to find")
If myText = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(What:=myText, LookIn:=xlValues, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address
Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select
myF = MsgBox("Found one """ & myText & """ here!" & vbLf & vbLf & _
thisLoc, vbInformation + vbOKCancel, "Found!")
If myF = 2 Then GoTo myQuit
Set Found = .UsedRange.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
Next ws
If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbLf & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
myEnd:
myRD = MsgBox("Search Again?", vbInformation + vbOKCancel, "Re-Run?")
If myRD = 1 Then GoTo myAgain
myQuit:
End Sub

 

[baitmaster]

data in range A2:A20
input cell is A1
formula in B2, copied down to B20:

=NOT(ISERROR(SEARCH($A$1,A2)))

This will return true/false to show any row that contains the value in A1. You can use this either with conditional formatting to show that row in colour, or within a macro that uses autofilter to hide any row returning "false"

You could link this to some simple code that uses an ActiveX control, or even cell input, to automatically run the autofilter for you. The code would probably be much simpler than what you are looking at, because the main work is done by the formulas and autofilter

 

[baitmaster]

I added the following sections to create a fully working filter / search tool:

1) named a cell "searchBox"
2) named a 2-column table of data "filterList", applied autofilter to it, and entered the formula posted above into the second column
3) added the following event code to the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("searchbox")) Is Nothing Then Range("filterlist").AutoFilter Field:=2, Criteria1:="TRUE"
End Sub

 

[baitmaster]

a further update to the code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("searchbox")) Is Nothing Then
    If Range("searchbox") = "" Then
        On Error Resume Next ' in case autofilter not applied
        ActiveSheet.ShowAllData
    Else
        Range("filterlist").AutoFilter Field:=2, Criteria1:="TRUE"
    End If
End If
End Sub

This update clears the autofilter if the search term is deleted. Allows null cells values to become visible when search box cleared

 

[smichael]

Thanks for the help! Say if I had a data range with 6 columns, I'd post the formula in the sixth column and change the range?

 

[baitmaster]

The keys here are (1) the TRUE/FALSE answer returned by the formula, and (2) the macro that runs the filter

In this case you would need a small change the macro as it is changing column 2 of the autofilter - change that as necessary

The other more important change, is to the formula. Search will only work on one string of text. You could use CONCATENATE to merge multiple cells into one string that is being looked at - e.g. "=NOT(ISERROR(SEARCH(searchBox,CONCATENATE(C8,D8,E8))))"

If you add many more columns, you might need to consider creating a custom function, so long as your formula reports T/F where you need it to

 

[smichael]

Everything seems to be working except the filter is only working for data in the first column. How would I change this to include data from say A2:C20?

 

[baitmaster]

check:

• your named ranges are correct
• your autofilter applies to all columns you want it to
• you are using the correct column number within the autofilter code
• your T/F formula looks at every cell you want it to, and returns the correct T/F you are interested in

From your question, it sounds like point 4 is not returning the correct answer - if not, your formula is probably wrong, or maybe you have calculations switched off

 

[smichael]

okay i got it to work while practicing, but after applying a similar code to my document i get:

Run-Time error '1004'
AutoFilter method of Range class failed

My range of data is A7:G1752 and my autofilter for the T/F column is in column H. I'm not sure why its not working, but my code is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
    If Range("A3") = "" Then
        On Error Resume Next ' in case autofilter not applied
        ActiveSheet.ShowAllData
    Else
        Range("A7:G1752").AutoFilter Field:=8, Criteria1:="TRUE"
    End If
End If
End Sub

Thanks for the help once again!

 

[baitmaster]

Range A:G contains 7 columns, you are trying to change column 8, which doesn't exist