how to add search criteria in VBA, EXCEL
 

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

how to add search criteria in VBA, EXCEL
 

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" wrote in message
...
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

how to add search criteria in VBA, EXCEL
 

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" wrote in message
...
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" wrote in message
...
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

how to add search criteria in VBA, EXCEL
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

how to add search criteria in VBA, EXCEL
 

Dear bob,

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

Thanks
JIANG

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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