| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like Operator Error
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 -- Cheers, Ryan |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Like Operator Error
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" wrote: 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 -- Cheers, Ryan |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Like Operator Error
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" wrote: 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" wrote: 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 -- Cheers, Ryan |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Like Operator Error
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" wrote in message ... 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" wrote: 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" wrote: 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 -- Cheers, Ryan |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Like Operator Error
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" wrote in message ... 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 -- Cheers, Ryan |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Like Operator Error
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" wrote: 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" wrote in message ... 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 -- Cheers, Ryan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vba using the OR operator | Excel Programming | |||
| Compare Validation Operator give error | Excel Programming | |||
| corrupt program or file? How to tell? Or is it operator error? | Excel Discussion (Misc queries) | |||
| Syntax error (missing operator) in query expression '6 Wescott Rd# | Excel Discussion (Misc queries) | |||
| OR Operator | Excel Worksheet Functions | |||
Hybrid Mode
