String manipulation 1

[Ablecken]

Hey guys,

I think that I am having some sort of brain fart. I have a string of numbers say 'ACD'. The string can be anywhere from '' to 'ABCDEFGHIJ'. But for right now it is just 'ACD'. I need to parse through the string letter by letter and find the first missing letter. In this case B. If the string was 'ABDEG' then it would return C. I only need one. Right now i have A-J in an array and i am searching through that with a known letter. For example:

    Do While counter < x
        'run through the array
        Do While y < 10
            current = Mid$(sProgIndex, counter2, 1)
            If Not current = aProgArray(y) Then
               'MsgBox aProgArray(y)
               'If Not y = 0 Then y = y - 1
               sCurrentIndex = aProgArray(y)
               Exit Do
            End If
             y = y + 1
             counter2 = counter2 + 1
        Loop
        counter = counter + 1
    Loop

I know that I am missing something. It runs thorugh and then works for the first two but it gives me ABB or something of the like. Any ideas?

 

[Schroeder]

How about converting them to their ASCII values?

Dim K As Integer
Dim intNextLetter As Integer

intNextLetter = Asc(Left(sProgIndex, 1)) + 1
For K = 2 To Len(sProgIndex)
If intNextLetter <> Asc(Mid(sProgIndex, K, 1) Then
MsgBox Chr(intNextLetter) & &quot; is missing.&quot;
End If
intNextLetter = Asc(Mid(sProgIndex, K, 1) + 1
Next

Or, change

intNextLetter = Asc(Mid(sProgIndex, K, 1) + 1

to

intNextLetter = intNextLetter + 1

if you want to catch every missing letter.

 

[ADoozer]

schroeder posted first, but ive written it now, so im gonna post...

Private Function FindMissingLetter(MyString As String) As String

Dim CurrentPosition As Integer
Dim FirstInSequence As Integer
Dim LastCharacterViewed As Integer

'for now lets force upper-case 65-90 UCASE 97-122 LCASE
MyString = UCase(MyString)

CurrentPosition = 1
FirstInSequence = Asc(Mid$(MyString, 1, 1))
LastCharacterViewed = FirstInSequence


For i = FirstInSequence To 90
If CurrentPosition = Len(MyString) Then GoTo NotFound
If Asc(Mid$(MyString, CurrentPosition + 1, 1)) = LastCharacterViewed + 1 Then
'in sequence
LastCharacterViewed = Asc(Mid$(MyString, CurrentPosition + 1, 1))
CurrentPosition = CurrentPosition + 1
Else
'not in sequence
FindMissingLetter = Chr(i + 1)
Exit Function
End If
Next i

NotFound:

'if code gets here no missing character found so return next in sequence
FindMissingLetter = Chr(i + 1)

End Function

the only thing different is my version catches upper and lower case (well forces upper case) and has a default for if no gaps are found!

good luck!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
To get the best response to a question, please check out FAQ222-2244 first
A General Guide To Excel in VB FAQ222-3383

 

[Ablecken]

Awsome thanx soo much adoozer

 

[ADoozer]

stuff i forgot in my haste to post.

1) you need to check for a zero length strings b4 using the asc function

2) also if the string has no missing letters and ends in Z &quot;XYZ&quot; you will need to catch this in the NotFound bit or the function will return &quot;[&quot;

Private Function FindMissingLetter(MyString As String) As String
    
    Dim CurrentPosition As Integer
    Dim FirstInSequence As Integer
    Dim LastCharacterViewed As Integer
    
    'make sure a string of characters exists
    If MyString = "" Then
        FindMissingLetter = "A"
        Exit Function
    End If
    
    'for now lets force  upper-case 65-90 UCASE 97-122 LCASE
    MyString = UCase(MyString)
    
    CurrentPosition = 1
    FirstInSequence = Asc(Mid$(MyString, 1, 1))
    LastCharacterViewed = FirstInSequence
    
    
    For i = FirstInSequence To 90
        If CurrentPosition = Len(MyString) Then GoTo NotFound
        If Asc(Mid$(MyString, CurrentPosition + 1, 1)) = LastCharacterViewed + 1 Then
            'in sequence
            LastCharacterViewed = Asc(Mid$(MyString, CurrentPosition + 1, 1))
            CurrentPosition = CurrentPosition + 1
        Else
            'not in sequence
            FindMissingLetter = Chr(i + 1)
            'return full string if required
'            mystring = Mid$(MyString, 1, CurrentPosition) & Chr(i + 1) & Mid$(MyString, CurrentPosition + 1, Len(MyString) - currentpossition)
            Exit Function
        End If
    Next i

NotFound:

'    if code gets here no missing character found so return next in sequence
    If Chr(i) = 90 Then
        FindMissingLetter = ""
    Else
        FindMissingLetter = Chr(i + 1)
    End If
    
End Function

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
To get the best response to a question, please check out FAQ222-2244 first
A General Guide To Excel in VB FAQ222-3383

 

[DrJavaJoe]

My 2 cents:

Option Explicit

Private Sub Command1_Click()
Dim strTest As String
Dim i As Integer
strTest = &quot;AbCdEfHJ&quot;
For i = 1 To Len(strTest) - 1
If Asc(UCase(Mid(strTest, i, 1))) + 1 <> Asc(UCase(Mid(strTest, i + 1, 1))) Then
MsgBox UCase(Chr(Asc(Mid(strTest, i, 1)) + 1)) & &quot; is missing&quot;
Exit For
End If
Next i
End Sub

 

[johnwm]

Public Function FindMissing(myWord As String) As String
Dim myCount As Long
Dim myLen As Long
myLen = Len(myWord)
If myLen = 0 Then FindMissing = &quot;A&quot;
myCount = 1
Do While FindMissing = &quot;&quot; And myCount <= myLen
Debug.Print &quot;mid = &quot; & Mid(myWord, myCount, 1) & &quot; num = &quot; & Chr$(64 + myCount)
If Mid(myWord, myCount, 1) <> Chr$(64 + myCount) Then
FindMissing = Chr$(64 + myCount)
End If
myCount = myCount + 1
Loop
End Function


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[ADoozer]

hehe... during my extensive 2 minutes of testing (myself DrJJ and Johnwm) i found, my code to be the slowest at around 3*10^-5s wheras DrJJ around 2*10^-5s and johnwms around 1*10^-5s

o well!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
To get the best response to a question, please check out FAQ222-2244 first
A General Guide To Excel in VB FAQ222-3383