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!

--
Sig: Say no to fixed width HTML tables. They look terrible in most browsers.

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

Sub TestIt()
Dim v, j
v = Array("W2424", "W2424P", "W3D2412", "W1836PLR", "DW362424L",
"W3D1560DTL")
For j = LBound(v) To UBound(v)
Debug.Print v(j); " - "; LastGroupOfNumbers(v(j))
Next j
End Sub

Returns:
W2424 - 2424
W2424P - 2424
W3D2412 - 2412
W1836PLR - 1836
DW362424L - 362424
W3D1560DTL - 1560

HTH
--
Dana DeLouis
Win XP & Office 2003


"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!

--
Sig: Say no to fixed width HTML tables. They look terrible in most
browsers.

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

Sub TestIt()
Dim v, j
v = Array("W2424", "W2424P", "W3D2412", "W1836PLR", "DW362424L",
"W3D1560DTL")
For j = LBound(v) To UBound(v)
Debug.Print v(j); " - "; LastGroupOfNumbers(v(j))
Next j
End Sub

Returns:
W2424 - 2424
W2424P - 2424
W3D2412 - 2412
W1836PLR - 1836
DW362424L - 362424
W3D1560DTL - 1560

HTH
--
Dana DeLouis
Win XP & Office 2003


"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!

--
Sig: Say no to fixed width HTML tables. They look terrible in most
browsers.

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

Sub TestIt()
Dim v, j
v = Array("W2424", "W2424P", "W3D2412", "W1836PLR", "DW362424L",
"W3D1560DTL")
For j = LBound(v) To UBound(v)
Debug.Print v(j); " - "; LastGroupOfNumbers(v(j))
Next j
End Sub

Returns:
W2424 - 2424
W2424P - 2424
W3D2412 - 2412
W1836PLR - 1836
DW362424L - 362424
W3D1560DTL - 1560

HTH
--
Dana DeLouis
Win XP & Office 2003


"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!

--
Sig: Say no to fixed width HTML tables. They look terrible in most
browsers.

I'm not Dana, but you could use it an adjacent cell as another worksheet
function:

=LastGroupOfNumbers(a1)

And drag down the column, well, if your data is in a column.

And you could actually update the values in place (overwriting the original
values) if you wanted via a macro.

Sub testme()
Dim myCell As Range
Dim myRng As Range
Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.Value = LastGroupOfNumbers(.Value)
End With
Next myCell
End Sub

It kind of depends on what you meant...



Biff wrote:

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

Sub TestIt()
Dim v, j
v = Array("W2424", "W2424P", "W3D2412", "W1836PLR", "DW362424L",
"W3D1560DTL")
For j = LBound(v) To UBound(v)
Debug.Print v(j); " - "; LastGroupOfNumbers(v(j))
Next j
End Sub

Returns:
W2424 - 2424
W2424P - 2424
W3D2412 - 2412
W1836PLR - 1836
DW362424L - 362424
W3D1560DTL - 1560

HTH
--
Dana DeLouis
Win XP & Office 2003


"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!

--
Sig: Say no to fixed width HTML tables. They look terrible in most
browsers.



--

Dave Peterson

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

Sub TestIt()
Dim v, j
v = Array("W2424", "W2424P", "W3D2412", "W1836PLR", "DW362424L",
"W3D1560DTL")
For j = LBound(v) To UBound(v)
Debug.Print v(j); " - "; LastGroupOfNumbers(v(j))
Next j
End Sub

Returns:
W2424 - 2424
W2424P - 2424
W3D2412 - 2412
W1836PLR - 1836
DW362424L - 362424
W3D1560DTL - 1560

HTH
--
Dana DeLouis
Win XP & Office 2003


"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!

--
Sig: Say no to fixed width HTML tables. They look terrible in most
browsers.

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!

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!

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!

On Mon, 1 Aug 2005 21:26:26 -0400 in article <OJ$9vEwlFHA.708
@TK2MSFTNGP09.phx.gbl, spoke thusly...
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.

If VBScript Regex is not in the list of references already, add the dll c:
\winnt\system32\vbscript.dll. That's what I had to do.

--
Sig: Say no to fixed width HTML tables. They look terrible in most browsers.

On Mon, 1 Aug 2005 17:12:48 -0400 in article <OqoD$2tlFHA.1412
@TK2MSFTNGP09.phx.gbl, spoke thusly...
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

Sub TestIt()
Dim v, j
v = Array("W2424", "W2424P", "W3D2412", "W1836PLR", "DW362424L",
"W3D1560DTL")
For j = LBound(v) To UBound(v)
Debug.Print v(j); " - "; LastGroupOfNumbers(v(j))
Next j
End Sub

Returns:
W2424 - 2424
W2424P - 2424
W3D2412 - 2412
W1836PLR - 1836
DW362424L - 362424
W3D1560DTL - 1560

HTH

Does the LastGroupOfNumbers function have to be global or declared globally?
I have a reference to Microsoft VBScript Regex 5.5 under References, but in
the worksheet I get this error "#NAME". Column B4 is my part number, column
A4 is using the function like this: =LastGroupofNumbers(B4)

What could I be doing wrong here?

Thanks for your help.
Chuck

--
Sig: Say no to fixed width HTML tables. They look terrible in most browsers.

I just copied Dana's code and pasted into a General module (not behind a
worksheet and not behind ThisWorkbook). Then I added that reference and it
worked fine for me.

Did you put it in a General module?

"Mr. Me" wrote:

On Mon, 1 Aug 2005 17:12:48 -0400 in article <OqoD$2tlFHA.1412
@TK2MSFTNGP09.phx.gbl, spoke thusly...
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

Sub TestIt()
Dim v, j
v = Array("W2424", "W2424P", "W3D2412", "W1836PLR", "DW362424L",
"W3D1560DTL")
For j = LBound(v) To UBound(v)
Debug.Print v(j); " - "; LastGroupOfNumbers(v(j))
Next j
End Sub

Returns:
W2424 - 2424
W2424P - 2424
W3D2412 - 2412
W1836PLR - 1836
DW362424L - 362424
W3D1560DTL - 1560

HTH

Does the LastGroupOfNumbers function have to be global or declared globally?
I have a reference to Microsoft VBScript Regex 5.5 under References, but in
the worksheet I get this error "#NAME". Column B4 is my part number, column
A4 is using the function like this: =LastGroupofNumbers(B4)

What could I be doing wrong here?

Thanks for your help.
Chuck

--
Sig: Say no to fixed width HTML tables. They look terrible in most browsers.

--

Dave Peterson

On Tue, 02 Aug 2005 08:17:46 -0500 in article <42EF727A.46BC3CA2
@verizonXSPAM.net, spoke thusly...
I just copied Dana's code and pasted into a General module (not behind a
worksheet and not behind ThisWorkbook). Then I added that reference and it
worked fine for me.

Did you put it in a General module?


I don't know. But when I hit alt-F11 it opened a VBA window, which happened
to be in personal.xls, which holds all my global macros. I don't know why it
opened personal.xls since my real worksheet had the focus at the time. So the
first time I pasted it there. So I deleted it from personal.xls and pasted it
into my real workbook and it seems to be working.

Thanks.

--
Sig: Say no to fixed width HTML tables. They look terrible in most browsers.

... So I deleted it from personal.xls and pasted it
into my real workbook and it seems to be working.

Hi. Glad it's working :) Just some added info. Since you placed that
function into another workbook, and that workbook was not an Add-Inn, then
you need to point to the workbook that has that function. For example, I
believe the following would have worked in your case.

=PERSONAL.XLS!LastGroupOfNumbers(A1)

A good technique is to have Excel enter the function for you. This helps as
a check also when writing new functions.
On the worksheet, chick the "Insert Function button next to the formula bar
(Fx)
Select the "User Defined" category.
Now, look for your function. This will enter the complete reference.
If you don't find your function, then it may be a clue that the function was
placed incorrectly on a worksheet module, so something similar.
HTH. :)
--
Dana DeLouis
Win XP & Office 2003


"Mr. Me" wrote in message
om...
On Tue, 02 Aug 2005 08:17:46 -0500 in article <42EF727A.46BC3CA2
@verizonXSPAM.net, spoke thusly...
I just copied Dana's code and pasted into a General module (not behind a
worksheet and not behind ThisWorkbook). Then I added that reference and
it
worked fine for me.

Did you put it in a General module?


I don't know. But when I hit alt-F11 it opened a VBA window, which
happened
to be in personal.xls, which holds all my global macros. I don't know why
it
opened personal.xls since my real worksheet had the focus at the time. So
the
first time I pasted it there. So I deleted it from personal.xls and pasted
it
into my real workbook and it seems to be working.

Thanks.

--
Sig: Say no to fixed width HTML tables. They look terrible in most
browsers.

And just to add to Dana's response...

You may want to keep the code in your personal.xls workbook. Then you'll be
able to use this function in any workbook you open (follow Dana's syntax,
though).

But if you share the workbook with this formula in it, it could get messy. (I'd
convert the formulas to values before I share.)

Dana DeLouis wrote:

... So I deleted it from personal.xls and pasted it
into my real workbook and it seems to be working.

Hi. Glad it's working :) Just some added info. Since you placed that
function into another workbook, and that workbook was not an Add-Inn, then
you need to point to the workbook that has that function. For example, I
believe the following would have worked in your case.

=PERSONAL.XLS!LastGroupOfNumbers(A1)

A good technique is to have Excel enter the function for you. This helps as
a check also when writing new functions.
On the worksheet, chick the "Insert Function button next to the formula bar
(Fx)
Select the "User Defined" category.
Now, look for your function. This will enter the complete reference.
If you don't find your function, then it may be a clue that the function was
placed incorrectly on a worksheet module, so something similar.
HTH. :)
--
Dana DeLouis
Win XP & Office 2003

"Mr. Me" wrote in message
om...
On Tue, 02 Aug 2005 08:17:46 -0500 in article <42EF727A.46BC3CA2
@verizonXSPAM.net, spoke thusly...
I just copied Dana's code and pasted into a General module (not behind a
worksheet and not behind ThisWorkbook). Then I added that reference and
it
worked fine for me.

Did you put it in a General module?


I don't know. But when I hit alt-F11 it opened a VBA window, which
happened
to be in personal.xls, which holds all my global macros. I don't know why
it
opened personal.xls since my real worksheet had the focus at the time. So
the
first time I pasted it there. So I deleted it from personal.xls and pasted
it
into my real workbook and it seems to be working.

Thanks.

--
Sig: Say no to fixed width HTML tables. They look terrible in most
browsers.

--

Dave Peterson