Find first Lower Case Letter in String - VBA Conumdrum!

[Peter.Stevens2]

Hi,

I'm having a nightmare with a VBA problem and wondered if any geniusses out there would be able to help me! Basically I want to compare 2 lists of names from different sources but both are in different formats. One list has the format: First name, Last Name (e.g. Peter Stevens) the other list is in the format LAST NAME, First Name (e.g. STEVENS Peter). There are some people in the list with double barrelled surnames (e.g. SMITH JONES) so I cant use the space character as a separator. The only way I can see of separating the two would be to locate the first occurance of a lowercase letter in the second List. Once I've found the character number in the string I can separate the two names out into the correct format of: First Name, Last Name using the Instr method.

I've written the code below to try and find it but I cant get it to work, the loop just goes all the way through the string and never finds a match. Any pointers would be really appreciated! Thanks

Option Explicit
Option Compare Binary
 
Sub Test1()
 
Dim StrLen As Integer
Dim ChrNum As Integer
Dim i As Integer
Dim j As Integer
Dim Exitfor As Boolean
Dim str As String
Dim SearchChar As String
 
StrLen = Len(Selection)
str = Selection.Value
For i = 1 To StrLen
 
    Exitfor = False
    For j = 97 To 122 '97-122 lower case ASCII charachers
 
        SearchChar = Chr(j)
        If InStr(i, str, SearchChar, vbBinaryCompare) = 1 Then
            Exitfor = True ' if this equals true the exit loop
            Exit For
        End If
    Next j
 
    If Exitfor = True Then Exit For
 
Next i
 
MsgBox i ' msgbox to return character number of first lower case letter in 
string
 
End Sub

 

[Joe4]

Look like your co-worker asked the same question.
You can follow it over here:
http://www.mrexcel.com/forum/showthread.php?t=455279