Why not use autofilter with an input box and filter kinda like

Sub Test_Me()
Application.ScreenUpdating = False
UserVal = Application.InputBox("Enter Search String")
If UserVal = False Then
Exit Sub
Else
Selection.AutoFilter Field:=1, Criteria1:="*" & UserVal & "*"
End If
Application.ScreenUpdating = True
End Sub


will filter the first autofiltered column where you put in the search
criteria in an input box
You can attach the macro to a button and have another button to reset the
autofilter
It doesn't make sense to both have very complicated formulas then using
macros as well when it can be done
with a simple autofilter and macro, the following will reset the autofilter
and select cell A1

Sub Reset_Filter()
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.FilterMode Then
On Error Resume Next
sh.ShowAllData
End If
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub

--
Regards,

Peo Sjoblom

(No private emails please)


"YJL" wrote in message
...

Thanks Biff. I have the filtering run perfectly.

Now, I insert a textbox and two common button-"search" and "clear".
I want user to key-in their text in the text box and press "search"
command button; or press "clear" command button to clear the input
text.

I did it in very primitive way. I used macro to record my routine. 1.
copy text in the textbox. 2.paste it to filtering sheet.

And it doesnt work out as i wish. Can it be done with macro??

Thanks

YJL


--
YJL
------------------------------------------------------------------------
YJL's Profile:
http://www.excelforum.com/member.php...o&userid=28693
View this thread: http://www.excelforum.com/showthread...hreadid=484200