WHAT COULD BE THE REASON FOR VB SCRIPT REGULAR EXPRESION TO WORK O

[Guest]

DEAR ALL,

I copied about 10 names from the thousands i have and ran the below code
(not my brains but got assistance thanks to Mr. Ron Rosenfield, Gary
student, Jim Thomlinson, Kevin Jones, Mike and Peter Richardson. Pls forgive
me for not following up all your help and assitence on account of my
ignorance of the delay in uploading by google.

By the time I was intimated, the thread has gone cold. Consequently I read
up lot on regexes and sort of got a hang of what the code is about.

Some sugested with inStrRev and split for which I get compile error as my
company provided only excel97.


I AM USING EXCEL97.

FIRST CODE:

This below code did shift the initials to the end of the name but ran once
and does not work again.

Public Sub ConvertNames()
'THIS WORKED FOR ONCE AND SECOND TIME NOT WORKING WHAT COULD BE REASON
Dim RegEx As Object
Dim Cell As Range
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.MultiLine = True
RegEx.IgnoreCase = True
RegEx.Pattern = "^([a-z..]*\.)([a-z]+)$"

For Each Cell In Selection
Cell = RegEx.Replace(Cell, "$2 $1")
Next Cell

End Sub


SECOND CODE:

This below code works fine but the initials goes in the middle if I have two
names.

Option Explicit
Sub MoveInit()

'given by ron rosenfield
Dim c As Range
Dim oRegex As Object
Dim oMatchCollection As Object
Dim i As Long
Const sPattern As String = "(([A-Z]\.\s?)*)(\w+)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = True
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).Value = oRegex.Replace(c.Text, "$3 $1")
Next c
End Sub

So pls help me in
A) What must have gone wrong in First code.
B) What must be the code to put the initials at the end if the cell has two
names like
A.S.D.PETER GREG.

Needless to say that I will be watching for all the post continuously all
day long unlike my earlier posts on this same subject.

brgds/captgnvr

 

[Don Guillett]

Please don't type in all CAPS (including subject line) as it is considered
shouting and rude.
Put this in a REGULAR module.
Public Function StrReverse(reverseString As String) As String
Dim i As Long
For i = Len(reverseString) To 1 Step -1
StrReverse = StrReverse & Mid(reverseString, i, 1)
Next i
End Function

 

[Guest]

D/Don
Ur advice reg caps noted. It is bcos of my eyesight and the fonts in these
groups are so small, some important ones i use caps.

I was just waiting for some guidance and as u sugested I tried with the
function. But the whole name also gets reversed. For example A.Z.RED becomes
DER.Z.A.

As I mentioned the second works fine but puts the initials in the middle.
so pls sugest steps to change the name A.S.D.PETER GREG to PETER GREG A.S.D.

For you information some names have space after the initials and in some no
space.

Looking forward to knowing ur advice n thnks for the first step.

Please don't type in all CAPS (including subject line) as it is considered
shouting and rude.
Put this in a REGULAR module.
Public Function StrReverse(reverseString As String) As String
Dim i As Long
For i = Len(reverseString) To 1 Step -1
StrReverse = StrReverse & Mid(reverseString, i, 1)
Next i
End Function


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

DEAR ALL,

I copied about 10 names from the thousands i have and ran the below code
(not my brains but got assistance thanks to Mr. Ron Rosenfield, Gary
student, Jim Thomlinson, Kevin Jones, Mike and Peter Richardson. Pls
forgive
me for not following up all your help and assitence on account of my
ignorance of the delay in uploading by google.

By the time I was intimated, the thread has gone cold. Consequently I
read
up lot on regexes and sort of got a hang of what the code is about.

Some sugested with inStrRev and split for which I get compile error as my
company provided only excel97.


I AM USING EXCEL97.

FIRST CODE:

This below code did shift the initials to the end of the name but ran once
and does not work again.

Public Sub ConvertNames()
'THIS WORKED FOR ONCE AND SECOND TIME NOT WORKING WHAT COULD BE REASON
Dim RegEx As Object
Dim Cell As Range
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.MultiLine = True
RegEx.IgnoreCase = True
RegEx.Pattern = "^([a-z..]*\.)([a-z]+)$"

For Each Cell In Selection
Cell = RegEx.Replace(Cell, "$2 $1")
Next Cell

End Sub


SECOND CODE:

This below code works fine but the initials goes in the middle if I have
two
names.

Option Explicit
Sub MoveInit()

'given by ron rosenfield
Dim c As Range
Dim oRegex As Object
Dim oMatchCollection As Object
Dim i As Long
Const sPattern As String = "(([A-Z]\.\s?)*)(\w+)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = True
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).Value = oRegex.Replace(c.Text, "$3 $1")
Next c
End Sub

So pls help me in
A) What must have gone wrong in First code.
B) What must be the code to put the initials at the end if the cell has
two
names like
A.S.D.PETER GREG.

Needless to say that I will be watching for all the post continuously all
day long unlike my earlier posts on this same subject.

brgds/captgnvr

 

[Ron Rosenfeld]

DEAR ALL,

I copied about 10 names from the thousands i have and ran the below code
(not my brains but got assistance thanks to Mr. Ron Rosenfield, Gary
student, Jim Thomlinson, Kevin Jones, Mike and Peter Richardson. Pls forgive
me for not following up all your help and assitence on account of my
ignorance of the delay in uploading by google.

By the time I was intimated, the thread has gone cold. Consequently I read
up lot on regexes and sort of got a hang of what the code is about.

Some sugested with inStrRev and split for which I get compile error as my
company provided only excel97.


I AM USING EXCEL97.

FIRST CODE:

This below code did shift the initials to the end of the name but ran once
and does not work again.

Public Sub ConvertNames()
'THIS WORKED FOR ONCE AND SECOND TIME NOT WORKING WHAT COULD BE REASON
Dim RegEx As Object
Dim Cell As Range
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.MultiLine = True
RegEx.IgnoreCase = True
RegEx.Pattern = "^([a-z..]*\.)([a-z]+)$"

For Each Cell In Selection
Cell = RegEx.Replace(Cell, "$2 $1")
Next Cell

End Sub


SECOND CODE:

This below code works fine but the initials goes in the middle if I have two
names.

Option Explicit
Sub MoveInit()

'given by ron rosenfield
Dim c As Range
Dim oRegex As Object
Dim oMatchCollection As Object
Dim i As Long
Const sPattern As String = "(([A-Z]\.\s?)*)(\w+)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = True
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).Value = oRegex.Replace(c.Text, "$3 $1")
Next c
End Sub

So pls help me in
A) What must have gone wrong in First code.
B) What must be the code to put the initials at the end if the cell has two
names like
A.S.D.PETER GREG.

Needless to say that I will be watching for all the post continuously all
day long unlike my earlier posts on this same subject.

brgds/captgnvr

Had you read my first response completely, you would have seen that I asked you
that very question of what you wanted for output if there were multiple names.


Try this for your multiple names issue:

========================================
Option Explicit
Sub MoveInit()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "(([A-Z]\.\s?)*)(.*)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = False
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).Value = oRegex.Replace(c.Text, "$3 $1")
Next c
End Sub
============================================
--ron

 

[Guest]

DRon
Yessssssss. It works perfect and thanks for ur guidance. I hv replied to
the original post answering if the cell has two names. Further I hv also
sent one email also requesting for little bit of explanations. Subsequently
I managed to find on google about regexp and figured out a bit.

This is will be complete if you could pls clarify how u arrived "$3 $1" in
the code
"""oRegex.Replace(c.Text, "$3 $1")""""

wrgds/captgnvr

DEAR ALL,

I copied about 10 names from the thousands i have and ran the below code
(not my brains but got assistance thanks to Mr. Ron Rosenfield, Gary
student, Jim Thomlinson, Kevin Jones, Mike and Peter Richardson. Pls forgive
me for not following up all your help and assitence on account of my
ignorance of the delay in uploading by google.

By the time I was intimated, the thread has gone cold. Consequently I read
up lot on regexes and sort of got a hang of what the code is about.

Some sugested with inStrRev and split for which I get compile error as my
company provided only excel97.


I AM USING EXCEL97.

FIRST CODE:

This below code did shift the initials to the end of the name but ran once
and does not work again.

Public Sub ConvertNames()
'THIS WORKED FOR ONCE AND SECOND TIME NOT WORKING WHAT COULD BE REASON
Dim RegEx As Object
Dim Cell As Range
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.MultiLine = True
RegEx.IgnoreCase = True
RegEx.Pattern = "^([a-z..]*\.)([a-z]+)$"

For Each Cell In Selection
Cell = RegEx.Replace(Cell, "$2 $1")
Next Cell

End Sub


SECOND CODE:

This below code works fine but the initials goes in the middle if I have two
names.

Option Explicit
Sub MoveInit()

'given by ron rosenfield
Dim c As Range
Dim oRegex As Object
Dim oMatchCollection As Object
Dim i As Long
Const sPattern As String = "(([A-Z]\.\s?)*)(\w+)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = True
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).Value = oRegex.Replace(c.Text, "$3 $1")
Next c
End Sub

So pls help me in
A) What must have gone wrong in First code.
B) What must be the code to put the initials at the end if the cell has two
names like
A.S.D.PETER GREG.

Needless to say that I will be watching for all the post continuously all
day long unlike my earlier posts on this same subject.

brgds/captgnvr

Had you read my first response completely, you would have seen that I asked you
that very question of what you wanted for output if there were multiple names.


Try this for your multiple names issue:

========================================
Option Explicit
Sub MoveInit()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "(([A-Z]\.\s?)*)(.*)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = False
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).Value = oRegex.Replace(c.Text, "$3 $1")
Next c
End Sub
============================================
--ron