Filter macro, using the..Begins With... criteria

_Ken_

Board Regular
Joined
Jul 26, 2002
Messages
53
Hi,
I have a large spreadsheet, comprising information on Roads and Parks, that is used by people who are not too computer literate and need things to be as simple as possible for them, they need to search for information and pass this on to other sections telephonically.
I am having problems trying to write a macro to filter the worksheet, using a UserForm with one TextBox and using the 'Begins With' criteria. Can anybody offer any assistance !!

Ken
This message was edited by _Ken_ on 2002-09-14 18:31
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Your post is cryptic!
You can use this "Find" macro to search all sheets for a match?

Sub myfind()
'Search all sheets for InputBox string.
'Run from Module.
Dim Message, Title, Default, SearchString
Message = "Enter your search string!" ' Set prompt.
Title = "Find ? On all sheets!" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If F Is Nothing Then
MsgBox "Not Found!"
Else
Location = F.Address
S.Select
Range(Location).Select
Exit For
End If
End With
Next S

End Sub


It will find cells that contain what you type, you can use as much or as little of the cell content as you want and it will still work. JSW
 

_Ken_

Board Regular
Joined
Jul 26, 2002
Messages
53
Joe,
That works but only seems to find the first instance of the search criteria, and not all.
I was looking for something similar to filtering, where all matches are shown at once. I just don't know how to get the macro to work for me, when using my own UserForm. I am trying to make things as easy as possible for my users, without having them to do the filtering the "normal way".

Ken
 

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
You could put something like this in the "okay" button<PRE>

Private Sub CommandButton1_Click()

Selection.AutoFilter Field:=1, Criteria1:=UserForm1.TextBox1.Value & "*", Operator:=xlAnd
Unload UserForm1

End Sub</PRE>

it takes the value of the textbox and slots it into the filtercriteria, adding a wildcard to ask for anything "beginning with"......

not sure of your userform make-up, but if you put this in the command button code, it'll kick in when the user clicks your "ok" command button on the userform

amend the "1" to be the number of the filtered column.... so if you have filtering on 6 columns and you want the 4th to apply, change it to a "4"


_________________
Hope this helps,
Chris
:)
[Excel '97; Windows ME]
This message was edited by Chris Davison on 2002-09-15 04:03
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255

ADVERTISEMENT

Here is a combination of what Chris and Joe suggested.

Last column (IV) will be used to mark the records containing the searchstring.
All found cells will get a conditional format for hiliting.

Sub myfind()
'Search current sheet for InputBox string.
Dim Message, Title, Default, SearchString

'Get string to search
Message = "Enter your search string!" ' Set prompt.
Title = "Find on this sheets!" ' set title
Default = "" ' Set default.
SearchString = InputBox(Message, Title, Default)

' remove any filters
ActiveSheet.AutoFilterMode = False
'remove all conditional formats
Cells.FormatConditions.Delete

' check if we have to search anything
If SearchString = "" Then Exit Sub

' find all cells containing InputBox string
Range("IV1:IV65536") = ""
With Range("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlPart, LookIn:=xlValues)
If F Is Nothing Then
MsgBox "Not Found!"
Else
firstLocation = F.Address
'apply conditinal format to cell
F.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
F.FormatConditions(1).Interior.ColorIndex = 6
Cells(F.Row, 256).Value = "X"
Do
Set F = .FindNext(F)
If Not F Is Nothing Then
Location = F.Address
'apply conditinal format to cell
F.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
F.FormatConditions(1).Interior.ColorIndex = 6
Cells(F.Row, 256).Value = "X"
End If
Loop While Not F Is Nothing And Location <> firstLocation
'apply filter to last column
Columns("IV:IV").AutoFilter Field:=1, Criteria1:="X"
End If
End With
End Sub

You can add some checkboxes for hiliting and searchmethod to your userform:
x match whole words
x hilite cells
And a button to remove any filters.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Rikrak,

I like your code, took out the cell formatting added some navagation Sub's to display and work the filter options and go back. Then built a simple test application to check it out. Good going it works real well. I think, more than the original poster will find it usefull. JSW
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
On 2002-09-15 09:33, Joe Was wrote:
Rikrak,

I like your code, took out the cell formatting added some navagation Sub's to display and work the filter options and go back. Then built a simple test application to check it out. Good going it works real well. I think, more than the original poster will find it usefull. JSW

Tip of week?
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Ken,

Try this macro (author unknown).

The macro searches the entire workbook, returns the number of "hits", and tabs to each hit when you press "OK".

Wildcards can be used e.g. *Smith will find all names with Smith (Fred Smith, Mike Smith etc.)

Sub SearchAllSheets()
Dim strSearchString As String
Dim ws As Worksheet
Dim foundCell As Range
Dim returnValue As Variant
Dim loopAddr As String
Dim countTot As Long
Dim counter As Long

strSearchString = InputBox(Prompt:= _
"Enter a title or other value to search for.", _
Title:="Search Workbook")

For Each ws In Worksheets
countTot = countTot + Application.CountIf( _
ws.UsedRange, "=" & strSearchString)
Next ws
If countTot = 0 Then
MsgBox strSearchString & " not found."
Else
counter = 0
For Each ws In Worksheets
With ws
.Activate
Set foundCell = .Cells.Find( _
What:=strSearchString, _
LookIn:=xlValues, _
LookAt:=xlPart)
If Not foundCell Is Nothing Then
loopAddr = foundCell.Address
Do
counter = counter + 1
foundCell.Activate
returnValue = MsgBox("Found " & strSearchString & _
" at " & foundCell.Address & vbNewLine & _
"(" & counter & " of " & countTot & ")", _
vbOKCancel)
If returnValue = vbCancel Then Exit For
Set foundCell = .Cells.FindNext( _
After:=foundCell)
Loop While Not foundCell Is Nothing And _
foundCell.Address <> loopAddr
End If
End With
Next ws
End If
End Sub

Regards,

Mike
 

Forum statistics

Threads
1,144,765
Messages
5,726,173
Members
422,659
Latest member
RGP268

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
Top