Hmmm....

Just as aside, many, many years ago I did a *little* programming in C and if
you needed to compile a library file (header file)with your code you simple
added a line of code to do that. Seems to me it would save a lot of what I
just experienced if VB(A) had this same capability. Or, maybe it already
does, I know hardly anything about VBA.

Biff

"Biff" wrote in message
...
OK, got it.

Works like a charm!

Thanks for your patience.

Biff

"Dana DeLouis" wrote in message
...
I get a compile error:
User-defined type not defined

Hi Biff.
In the vba editor, go to Tools | References.. |
and look for something similar to:

"Microsoft VBScript Regular Expressions 5.5"
Check this to add it to vba.

I see you have Excel 2000. Hopefully, it is there as I don't remember
anymore. :(
--
Dana DeLouis
Win XP & Office 2003


"Biff" wrote in message
...
Hi!

I get a compile error:

User-defined type not defined

With this part highlighted:

RE As RegExp

Biff

"Dana DeLouis" wrote in message
...
Hi. Sorry about the test sub. Just use it like a regular function on
the worksheet.

=LastGroupOfNumbers(A1)

I think this code can be made more efficient, but I just don't see it
at the moment... :(
The reason I pulled RE out of the function was in case you wanted to
use it in a macro. This way, RE wouldn't be initialized with each
call.
HTH
--
Dana DeLouis
Win XP & Office 2003


"Biff" wrote in message
...
Hi Dana!

How could this be used on a range and without the array elements being
predefined?

Biff

"Dana DeLouis" wrote in message
...
Hi. Here's just one way...

Option Explicit
Public RE As RegExp

Function LastGroupOfNumbers(s)
'// Microsoft VBScript Regular Expressions 5.5

Dim Matches As MatchCollection
Const k As String = "(\d+)\D*$"

If RE Is Nothing Then Set RE = New RegExp
With RE
.IgnoreCase = True
.Global = True
.Pattern = k

If .Test(s) Then
Set Matches = .Execute(s)
LastGroupOfNumbers = Matches(0).SubMatches(0)
End If
End With
End Function


<snip

"Mr. Me" wrote in message
om...
I have Excel 2000. I would like to get a Regexp function. I have
model
numbers that I want to sort. Model numbers are composed of a prefix
(variable
length), the model number (2-6 digits), and a suffix (variable
length). The
left, right and mid functions only work if the prefix is a fixed
length, thus
I need regexp to pick out the first group of 2-6 digits in a row,
then sort
on that.

SO if I had a function that would look at column A (with the whole
model #)
and put the modified, sortable model number in col B, that would be
great.

Example model #s and extracted sort key:
W2424 2424
W2424P 2424
W3D2412 2412
W1836PLR 1836
DW362424L 362424
W3D1560DTL 1560

Thanks!