how to add search criteria in VBA, EXCEL

[Kortrijker]

Dear all,

I have one table, which include 'name', 'age','class','comment','score' 5
fields, last time I asked how to make search criteria with vba, people
suggested me to make macro to record advanced filter in excel, but my
question is how to make the input of search demand recognized in vba. In one
word, I don't have clear idea to link different procedures together. I wish
I could have a clearer mind after this.

for instance,

selection. autofilter. Field:=1, criteria1:="=*jiang*", operator:=_
xlor, criteria2:="<>wu"

in the field 1, name, I want this table show recorders whose name contain
'jiang' or not equal 'wu'. but if input changes everytime, how could I make
it? For instance, a textbox which lets user input different name to
search result, how could I link input in the textbox with my criteria?

Thanks.
JIANG

 

[Bob Phillips]

JIang,

Does this help

Worksheets("Sheet1").Columns(1).AutoFilter Field:=1, _
Criteria1:="=*" & Userform1.TextBox1.Text & "*", _
Operator:=xlOr, _
Criteria2:="<>" & Userform1.TextBox2.Text

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Kortrijker]

Bob,

Thanks. This does help.

But I still don't understand how to make sure this autofilter works only in
Field of 'name', should I use 'Dim name as 1' first, then VBA would know
'Field 1' is the field of name.

Best regards,
JIANG

 

[Bob Phillips]

Kiang,

I am not sure I understand the question, but the code I supplied is getting
name from a userform textbox, as I thought that that was what was wanted.
Thus you don't have to Dim anything as it is an object.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Kortrijker]

Dear bob,

you are right, I don't need Dim anything.

Thanks
JIANG