Filter numbers with textbox using 123132*

[Marc]

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

 

[Bernie Deitrick]

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]

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













- 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

 

[Dave Peterson]

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]

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

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













--

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

 

[Bernie Deitrick]

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,

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













- 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

 

[Bernie Deitrick]

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

 

[Marc]

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

 

[Marc]

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

 

[Dave Peterson]

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]

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