Find first Lower Case Letter in String - VBA Conumdrum!

Peter.Stevens2

Board Regular
Joined
Sep 16, 2008
Messages
56
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

Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,136,433
Messages
5,675,829
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top