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

[_Ken_]

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

 

[Joe Was]

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_]

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]

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]

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]

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]

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]

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