Identify first lower case letter in string

melewie

Board Regular
Joined
Nov 21, 2008
Messages
185
Hi All,

I am having problems (again!!!). I am trying to idenify the first lower case letter in a string. Below is the cloest I have got (It doesnt work at all but doesnt debug)

Code:
[SIZE=2]Option Explicit
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
 
[COLOR=black][FONT=Verdana]StrLen = Len(Selection)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]str = Selection.Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For i = 1 To StrLen<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exitfor = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For j = 97 To 122<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If InStr(i, str, Chr(j), vbBinaryCompare) = 1 Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exitfor = True<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exit For<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox Chr(j)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Next j<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Exitfor = True Then Exit For<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Next i<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox i<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]
[/SIZE]
Any help would be great, thanks in advance
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Maybe:

Code:
Sub Test1()
Dim i As Integer
Dim str As String
 
str = Selection.Value
For i = 1 To Len(str)
If Mid(str, i, 1) <> UCase(Mid(str, i, 1)) Then
MsgBox "First lower case is: " & Mid(str, i, 1)
Exit Sub
End If
Next i
MsgBox "No lower case"
End Sub
Dom
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
HTML:
Option Explicit
Sub Test1()
Dim StrLen As Integer
Dim Exitfor As Boolean
Dim i As Integer
Dim j As Integer
Dim str As String

str = Selection.Value
StrLen = Len(str)
For i = 1 To StrLen
  j = Asc(Mid(str, i, 1))
  If j >= 97 And j <= 122 Then
    MsgBox i & ", " & Mid(str, i, 1)
    Exitfor = True
    Exit For
  End If
Next i
If Not Exitfor Then MsgBox "No lower case letter found"
End Sub
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
or for fun:
Code:
Sub test2()
Dim x
x = Evaluate("=MATCH(1,(CODE(MID(""" & Selection.Value & """,ROW(1:1000),1))>=97)*(CODE(MID(""" & Selection.Value & """,ROW(1:1000),1))<=122),0)")
If Not IsError(x) Then MsgBox x & ", " & Mid(Selection, x, 1) Else MsgBox "No lower case found"
End Sub
If you wanted this as just worksheet formulae instead:
if the string to be tested was in A1, then in any other cells, both ARRAY-ENTERED,
=MATCH(1,(CODE(MID(A1,ROW($1:$1000),1))>=97)*(CODE(MID(A1,ROW($1:$1000),1))<=122),0)
and
=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$1000),1))>=97)*(CODE(MID(A1,ROW($1:$1000),1))<=122),0),1)
will give the position and the letter in the string respectively.
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,003
For what it's worth, here's a similar way...

=MATCH(1,IF(ABS(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-109.5)<=12.5,1),0)

...confirmed with COTROL+SHIFT+ENTER.
 

melewie

Board Regular
Joined
Nov 21, 2008
Messages
185
Hi All,

Cheers for the help everyone. I will try this again today.

thanks again

melewie
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top