String Range Compare

[BlueDNA]

Hi all,

Tried to search for this in the forum, but didnt have much luck finding it. If someone knows please send me the link!

Im trying to see if a cells content is in a valid range.

For example, i need the cell content ID to be in the range:
ABC27XXXX to ABC49XXXX
where X is an an integer [0-9]

i was hoping to use something like:
[vba]
Dim myCell As String
if myCell LIKE [a-A][b-B][c-C][27-49][0-9][0-9][0-9][0-9] then
...
else
...
endif
[/vba]

But it doesnt work. how can i achieve this?

Thanks all,

David

[OBP]

What is wrong with the old fashioned
if mycell>" ABC27XXXX" and mycell<"ABC49XXXX" then
...

[OBP]

With this in cells a1 to a3
ABC29
ABC49
ABC50
this works
Sub Macro1()

Dim a As String, b As String, c As String
Range("a1").Select
a = ActiveCell
Range("a2").Select
b = ActiveCell
Range("a3").Select
c = ActiveCell
If b > a And b < c Then
MsgBox "Yes"
Else: MsgBox "no"
End If
End Sub

[Bob Phillips]

[vba]

Dim oRegEx As Object
Dim IsValid As Boolean

Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "[A-Za-z0-9]$"
IsValid= .Test(MyCell)
End With
Set oRegEx = Nothing

[/vba]

[BlueDNA]

What is wrong with the old fashioned
if mycell>" ABC27XXXX" and mycell<"ABC49XXXX" then
...

Because i didnt even know less than string comparison was even possible. Been into OOP for too long....VB is really a different world....a much simpler one to say the least.

Thanks a lot! Short and simple!

[stanl]

Before this gets marked solved, I have a question about XLD's pattern. As such

.Pattern = "[A-Za-z0-9]$"

appears to not trap the 27-49 needed by the 4-5th chars. I tried "(ABC[2-4])" which captures the 4th, but am confused about the 5th, and several attempts ended in OLE errors. Stan

[stanl]

.Pattern = "(ABC(2[7-9]|3[0-8]|4[0-9]))" 

just needed my coffee Stan

[BlueDNA]

Thanks all for the different methods and suggestions.


David.