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
What is wrong with the old fashioned
if mycell>" ABC27XXXX" and mycell<"ABC49XXXX" then
...
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.