PDA

View Full Version : String Range Compare



BlueDNA
04-30-2006, 08:21 PM
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:

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


But it doesnt work. how can i achieve this?

Thanks all,

David

OBP
05-01-2006, 02:13 AM
What is wrong with the old fashioned
if mycell>" ABC27XXXX" and mycell<"ABC49XXXX" then
...

OBP
05-01-2006, 03:03 AM
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
05-01-2006, 03:04 AM
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

BlueDNA
05-01-2006, 04:17 AM
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
05-01-2006, 07:35 AM
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.:dunno Stan

stanl
05-01-2006, 12:26 PM
.Pattern = "(ABC(2[7-9]|3[0-8]|4[0-9]))"


just needed my coffee:doh: Stan

BlueDNA
05-01-2006, 04:03 PM
Thanks all for the different methods and suggestions.
:clap:

David.