Need some help with search function

S

smichael

New Member
Joined
Jul 15, 2011
Messages
38
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:
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
data in range A2:A20
input cell is A1
formula in B2, copied down to B20:

Code: 
=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
 
Upvote 0
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:

Code: 
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
 
Upvote 0
a further update to the code
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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:

Code: 
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
search all worksheets based upon textbox value and add to listbox
Replies
7
Views
407
Jaye7
J
S
  • Solved
Set Found = Used Range change to look in Column H
Replies
3
Views
425
Alex Blakenburg
A
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
184
sijpie
sijpie
E
  • Question Question
Search function not finding rows containing the matching value
Replies
1
Views
671
GlennUK
G
P
  • Question Question
VBA- search within a search to return a specific selection.
Replies
6
Views
580
Joe4
Joe4

Forum statistics

Threads
1,224,521
Messages
6,179,290
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top