Like Operator Error

R

RyanH

I am getting an error on this single line of my code: Error #93, "Invalid
Pattern String". This If...Then Statement is used to scan Col.A and ensure
that the values have a particular format. It works great till it gets to
this number "18075-12". For some reason it is throwing the error and I don't
know why, any ideas?

Error >>Not .Cells(i, "A") Like "[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]"


If (Not .Cells(i, "A") Like
"[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]" And _
Not .Cells(i, "A") Like
"[0-9Aa][0-9Dd][0-9Tt]##-##[Aa-Zz][Aa-Zz]") And _
rngProductCodes.Find(What:=Right(.Cells(i, "A"), 2),
LookIn:=xlValues) Is Nothing Then

' highlight bad item number red and bold
With .Cells(i, "A").Font
.ColorIndex = 3
.Bold = True
End With
End If
 
B

Barb Reinhardt

Are you saying that the pattern you want to match is

"[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]"

Where is your wildcard?

Barb Reinhardt
 
R

Rick Rothstein

I think you want this pattern...

"[0-9Aa][0-9Dd][0-9Tt]##-#[A-Za-z][A-Za-z]"

Upper case and lower case ranges (separated by a dash) are specified
individually).
 
R

RyanH

I'm not sure what you mean by wildcard? I am wanting to ensure that each
cell has one of these formats:

#####-# + 2 Letters
#####-## + 2 Letters
ADT##-# + 2 Letters
ADT##-## + 2 Letters

I don't want the 2 Letters and the "ADT" to be case sensitive, I need a
range of Aa-Zx. Is this not the correct way to code a alphabetic range
[Aa-Zz]?
--
Cheers,
Ryan


Barb Reinhardt said:
Are you saying that the pattern you want to match is

"[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]"

Where is your wildcard?

Barb Reinhardt




RyanH said:
I am getting an error on this single line of my code: Error #93, "Invalid
Pattern String". This If...Then Statement is used to scan Col.A and ensure
that the values have a particular format. It works great till it gets to
this number "18075-12". For some reason it is throwing the error and I don't
know why, any ideas?

Error >>Not .Cells(i, "A") Like "[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]"


If (Not .Cells(i, "A") Like
"[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]" And _
Not .Cells(i, "A") Like
"[0-9Aa][0-9Dd][0-9Tt]##-##[Aa-Zz][Aa-Zz]") And _
rngProductCodes.Find(What:=Right(.Cells(i, "A"), 2),
LookIn:=xlValues) Is Nothing Then

' highlight bad item number red and bold
With .Cells(i, "A").Font
.ColorIndex = 3
.Bold = True
End With
End If
Click to expand...
Click to expand...
 
R

Rick Rothstein

Not, it is not the correct way to code the alphabetic range you want... see
my other post for the correct way to code it.

--
Rick (MVP - Excel)


RyanH said:
I'm not sure what you mean by wildcard? I am wanting to ensure that each
cell has one of these formats:

#####-# + 2 Letters
#####-## + 2 Letters
ADT##-# + 2 Letters
ADT##-## + 2 Letters

I don't want the 2 Letters and the "ADT" to be case sensitive, I need a
range of Aa-Zx. Is this not the correct way to code a alphabetic range
[Aa-Zz]?
--
Cheers,
Ryan


Barb Reinhardt said:
Are you saying that the pattern you want to match is

"[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]"

Where is your wildcard?

Barb Reinhardt




RyanH said:
I am getting an error on this single line of my code: Error #93,
"Invalid
Pattern String". This If...Then Statement is used to scan Col.A and
ensure
that the values have a particular format. It works great till it gets
to
this number "18075-12". For some reason it is throwing the error and I
don't
know why, any ideas?

Error >>Not .Cells(i, "A") Like
"[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]"


If (Not .Cells(i, "A") Like
"[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]" And _
Not .Cells(i, "A") Like
"[0-9Aa][0-9Dd][0-9Tt]##-##[Aa-Zz][Aa-Zz]") And _
rngProductCodes.Find(What:=Right(.Cells(i, "A"), 2),
LookIn:=xlValues) Is Nothing Then

' highlight bad item number red and bold
With .Cells(i, "A").Font
.ColorIndex = 3
.Bold = True
End With
End If
Click to expand...
Click to expand...
Click to expand...
 
R

RyanH

Sorry about that Rick. I was looking for that post and couldn't find it.
That is exactly what I was looking for!

Thanks for saving me again, you're the man!
--
Cheers,
Ryan


Rick Rothstein said:
I think you want this pattern...

"[0-9Aa][0-9Dd][0-9Tt]##-#[A-Za-z][A-Za-z]"

Upper case and lower case ranges (separated by a dash) are specified
individually).

--
Rick (MVP - Excel)


RyanH said:
I am getting an error on this single line of my code: Error #93, "Invalid
Pattern String". This If...Then Statement is used to scan Col.A and
ensure
that the values have a particular format. It works great till it gets to
this number "18075-12". For some reason it is throwing the error and I
don't
know why, any ideas?

Error >>Not .Cells(i, "A") Like "[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]"


If (Not .Cells(i, "A") Like
"[0-9Aa][0-9Dd][0-9Tt]##-#[Aa-Zz][Aa-Zz]" And _
Not .Cells(i, "A") Like
"[0-9Aa][0-9Dd][0-9Tt]##-##[Aa-Zz][Aa-Zz]") And _
rngProductCodes.Find(What:=Right(.Cells(i, "A"), 2),
LookIn:=xlValues) Is Nothing Then

' highlight bad item number red and bold
With .Cells(i, "A").Font
.ColorIndex = 3
.Bold = True
End With
End If
Click to expand...
Click to expand...
 
Top