Filter numbers with textbox using 123132*
 

Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc

Filter numbers with textbox using 123132*
 

Marc,

You need to get the syntax correct - the criteria string needs to be a valid equality statement

Criteria1:=Criteria1 & "*", _

should be

Criteria1:= "=" & Criteria1 & "*", _

etc.

HTH,
Bernie
MS Excel MVP


"Marc" wrote in message
ups.com...
Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc

Filter numbers with textbox using 123132*
 

On 31 Maj, 19:51, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Marc,

You need to get the syntax correct - the criteria string needs to be a valid equality statement

Criteria1:=Criteria1 & "*", _

should be

Criteria1:= "=" & Criteria1 & "*", _

etc.

HTH,
Bernie
MS Excel MVP

"Marc" wrote in message

ups.com...



Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Bernie,

Thanks for the quick reply.
I have tried what you said but it does not help. It doesn't return any
numbers at all, not even the ones
that matches entirely. It hides all the rows except the ones
containing "center".
The numbers in the range are all formated as numbers. There are some
text cells too.

Can you or anybody else help me?

I have changed it to but it does not work:

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String


Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()


Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:="=" &Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Best regards

Marc

Filter numbers with textbox using 123132*
 

If those are real numbers (not text) in your field to be filtered, then I don't
think the wild card stuff will work the way you want.

It that field is really text, then ignore this message.



Marc wrote:

Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc

--

Dave Peterson

Filter numbers with textbox using 123132*
 

On 31 Maj, 20:34, Dave Peterson wrote:
If those are real numbers (not text) in your field to be filtered, then I don't
think the wild card stuff will work the way you want.

It that field is really text, then ignore this message.





Marc wrote:

Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Dave,

Thanks for the tip.
They are real numbers, formated as numbers. There are no formulas in
the cells.

I hoped it would be possible. I know it is with text and wild cards.

Cheers

Marc

Filter numbers with textbox using 123132*
 

Marc,

Since they are numbers, you could use something like this if it is just the
last digit that is missing:

Criteria1:="=" & Criteria1 * 10, Operator:=xlAnd, _
Criteria2:="<" & (Criteria1 + 1) * 10

Or this, if you could have more than one digit missing

Criteria1:="=" & Criteria1 * 10^(CritLen - Len(Criteria1)),
Operator:=xlAnd, _
Criteria2:="<" & (Criteria1 + 1) * 10^(CritLen - Len(Criteria1))


HTH,
Bernie
MS Excel MVP




"Marc" wrote in message
oups.com...
On 31 Maj, 19:51, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Marc,

You need to get the syntax correct - the criteria string needs to be a
valid equality statement

Criteria1:=Criteria1 & "*", _

should be

Criteria1:= "=" & Criteria1 & "*", _

etc.

HTH,
Bernie
MS Excel MVP

"Marc" wrote in message

ups.com...



Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Bernie,

Thanks for the quick reply.
I have tried what you said but it does not help. It doesn't return any
numbers at all, not even the ones
that matches entirely. It hides all the rows except the ones
containing "center".
The numbers in the range are all formated as numbers. There are some
text cells too.

Can you or anybody else help me?

I have changed it to but it does not work:

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String


Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()


Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:="=" &Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Best regards

Marc

Filter numbers with textbox using 123132*
 

This was untested - Excel may want explicit conversion, so you may need

Criteria1:="=" & CLng(Criteria1) * 10, Operator:=xlAnd, _
Criteria2:="<" & (CLng(Criteria1) + 1) * 10

or use CDbl...

Bernie


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Marc,

Since they are numbers, you could use something like this if it is just
the last digit that is missing:

Criteria1:="=" & Criteria1 * 10, Operator:=xlAnd, _
Criteria2:="<" & (Criteria1 + 1) * 10

Or this, if you could have more than one digit missing

Criteria1:="=" & Criteria1 * 10^(CritLen - Len(Criteria1)),
Operator:=xlAnd, _
Criteria2:="<" & (Criteria1 + 1) * 10^(CritLen - Len(Criteria1))


HTH,
Bernie
MS Excel MVP




"Marc" wrote in message
oups.com...
On 31 Maj, 19:51, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Marc,

You need to get the syntax correct - the criteria string needs to be a
valid equality statement

Criteria1:=Criteria1 & "*", _

should be

Criteria1:= "=" & Criteria1 & "*", _

etc.

HTH,
Bernie
MS Excel MVP

"Marc" wrote in message

ups.com...



Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Bernie,

Thanks for the quick reply.
I have tried what you said but it does not help. It doesn't return any
numbers at all, not even the ones
that matches entirely. It hides all the rows except the ones
containing "center".
The numbers in the range are all formated as numbers. There are some
text cells too.

Can you or anybody else help me?

I have changed it to but it does not work:

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String


Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()


Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:="=" &Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Best regards

Marc

Filter numbers with textbox using 123132*
 

Hi Bernie,

Thanks for your suggestions.
The numbers are all between 7 and 9 digits and are systematical
generated. That means that the first three digits
refers to an exact division. The 4th and 5th a workcenter etc.
Therefor I think that "greater and smaller than" operators can not be
used as far as I can se.
I will try to work with your suggestions and report back.

Cheers

Marc


On 31 Maj, 22:02, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Marc,

Since they are numbers, you could use something like this if it is just the
last digit that is missing:

Criteria1:="=" & Criteria1 * 10, Operator:=xlAnd, _
Criteria2:="<" & (Criteria1 + 1) * 10

Or this, if you could have more than one digit missing

Criteria1:="=" & Criteria1 * 10^(CritLen - Len(Criteria1)),
Operator:=xlAnd, _
Criteria2:="<" & (Criteria1 + 1) * 10^(CritLen - Len(Criteria1))

HTH,
Bernie
MS Excel MVP

"Marc" wrote in message

oups.com...
On 31 Maj, 19:51, "Bernie Deitrick" <deitbe @ consumer dot org wrote:





Marc,

You need to get the syntax correct - the criteria string needs to be a
valid equality statement

Criteria1:=Criteria1 & "*", _

should be

Criteria1:= "=" & Criteria1 & "*", _

etc.

HTH,
Bernie
MS Excel MVP

"Marc" wrote in message

oups.com...

Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Bernie,

Thanks for the quick reply.
I have tried what you said but it does not help. It doesn't return any
numbers at all, not even the ones
that matches entirely. It hides all the rows except the ones
containing "center".
The numbers in the range are all formated as numbers. There are some
text cells too.

Can you or anybody else help me?

I have changed it to but it does not work:

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:="=" &Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Best regards

Marc- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Filter numbers with textbox using 123132*
 

The only solution I just thought of is to use a dummy column where I
use this kind of formula:
=left("The number from the original column",Len("NumberInTextBox)) for
each row and then filter on this column instead.

The thing is now, that I have at least 1000 rows and more to come and
Im trying to make it memory efficient and
keep the file size small.

But you know the feeling that it would be nice to get it to work the
real way...

Cheers


On 31 Maj, 21:33, Marc wrote:
On 31 Maj, 20:34, Dave Peterson wrote:





If those are real numbers (not text) in your field to be filtered, then I don't
think the wild card stuff will work the way you want.

It that field is really text, then ignore this message.

Marc wrote:

Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Dave,

Thanks for the tip.
They are real numbers, formated as numbers. There are no formulas in
the cells.

I hoped it would be possible. I know it is with text and wild cards.

Cheers

Marc- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Filter numbers with textbox using 123132*
 

If I want to use "contains" with real numbers, then I insert a new column and
use a formula like:

=""&a2

The ="" is enough to make the results text.

If you know how the cells are formatted, you may want:

=text(a2,"0000000.00")
or something

This will be text, also.

Marc wrote:

The only solution I just thought of is to use a dummy column where I
use this kind of formula:
=left("The number from the original column",Len("NumberInTextBox)) for
each row and then filter on this column instead.

The thing is now, that I have at least 1000 rows and more to come and
Im trying to make it memory efficient and
keep the file size small.

But you know the feeling that it would be nice to get it to work the
real way...

Cheers

On 31 Maj, 21:33, Marc wrote:
On 31 Maj, 20:34, Dave Peterson wrote:





If those are real numbers (not text) in your field to be filtered, then I don't
think the wild card stuff will work the way you want.

It that field is really text, then ignore this message.

Marc wrote:

Hi,

I have a problem with my code. I have made a userform with a textbox
and am trying to filter a set of numbers in a column.

I can get it to work when I write the exact number in the textbox
which
is also in the list with numbers. But I want to use the "*" asterisk.

Lets say if 123123 is in list and the textbox-value is 1. Then the
number should not be filtered/hidden.

Private Sub TextBoxWorkCenter_Change()
Dim Criteria1 As Double 'Tried As
String

Criteria1 = Val(TextBoxWorkCenter.Value) 'Tried using: & "*"
and without Val()

Worksheets("test1").Range("B6:B1000").AutoFilter _
field:=1, _
Criteria1:=Criteria1 & "*", _
Operator:=xlOr, _
Criteria2:="Center*", _
VisibleDropDown:=False
End Sub

Hope that somebody can help me????

Cheers

Marc

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Dave,

Thanks for the tip.
They are real numbers, formated as numbers. There are no formulas in
the cells.

I hoped it would be possible. I know it is with text and wild cards.

Cheers

Marc- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

--

Dave Peterson

Filter numbers with textbox using 123132*
 

Hi Dave,

Thanks for the suggestions. I figured it out as I tired what wrote in
the last message.

I set a cell to contain the number of characters in a cell (z3)
and then used =left(B6,$Z$3) in a dummy column and used autofilter on
that column.

Cheers

Marc