Solved Multiple If Statements Problem

S

silentwolf

Active member
Local time
Today, 02:55
Joined
Jun 12, 2009
Messages
644
Hi guys,

I know this has been discussed in the formum but for my issue I can not seam to make it work.

What I like to do is following

Code: 
Public Function ReturnUMS01(strText As String) As String

    If strText Like "IBAN*" Then
        If strText Like "*Zahlungsreferenz*" Then
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Zahlungsreferenz:") - 1
            mIntLength = mEndPos - mStartPos
    
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
            
        If strText Like "*Auftraggeberreferenz:*" Then
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
            mIntLength = mEndPos - mStartPos
    
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
        End If
        
        If strText Like "*IBAN*Auftraggeberreferenz*" Then
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
            mIntLength = mEndPos - mStartPos
    
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)       
        
        End If       

End Function

The condition works until the first Endif

What I like to do is to have multiple condition when the String does not Start with "IBAN" but it is still includet in the string.
And for thos situation I also need multiple desicions.

I tried but if the string does not start with "IBAN" it does not return anything it is not getting into the other if statement after the Endif

Maybe someone can help please?

Cheers
 
The function has 4 If and only 2 End If. There are two missing End If statements
 
maybe:
Code: 
Public Function ReturnUMS01(strText As String) As String

    If strText Like "IBAN*" Then
        If strText Like "*Zahlungsreferenz*" Then
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Zahlungsreferenz:") - 1
            mIntLength = mEndPos - mStartPos
   
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
            Exit Function
        If strText Like "*Auftraggeberreferenz:*" Then
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
            mIntLength = mEndPos - mStartPos
   
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
            Exit Function
        End If
       
        If strText Like "*IBAN*Auftraggeberreferenz*" Then
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
            mIntLength = mEndPos - mStartPos
   
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)      
       
        End If      

End Function
 
Hi guys,

unfortunately not working as yet :(
It is surely simple but I can not get my head around it ..pff
 
A String I like to find is something like this one
Code: 
"Allianz IBAN: AT70 1000 6666 7777 8888 Creditor-ID: AT25AEV00000004433 0 Auftraggeberreferenz: AEV21355-E00695611 SEPA-Lastschrift A559725496 Albert REF: 1200021122706174766477677966"

But it does not jump into the Statement .. no Error just does not go into the
if StrText Like "*IBAN*Auftraggeberreferenz*..........
 
on post #5 what is the text to be returned?
 
try:
Code: 
Public Function ReturnUMS01(strText As String) As String
    Dim i As Integer, j As Integer
    i = InStr(1, strText, "IBAN:")
    If i <> 0 Then
        i = InStr(1, strText, "Zahlungsreferenz:")
        If i <> 0 Then
            strText = Trim$(Mid$(strText, i + Len("Zahlungsreferenz:")))
            j = InStr(1, strText, " ")
            If j <> 0 Then
                ReturnUMS01 = Left$(strText, j-1)
            End If
            Exit Function
        End If
         
        i = InStr(1, strText, "Auftraggeberreferenz:")
        If i <> 0 Then
            strText = Trim$(Mid$(strText, i + Len("Auftraggeberreferenz:")))
            j = InStr(1, strText, " ")
            If j <> 0 Then
                ReturnUMS01 = Left$(strText, j-1)
            End If
        End If
    End If
End Function
 
As previously stated you are missing End If's - I'm surprised it compiles.
I often use select case statements for this type of thing:
Code: 
Public Function ReturnUMS01(strText As String) As String

    If strText Like "IBAN*" Then
        
        SELECT CASE strText
            CASE  "*Zahlungsreferenz*"
                mStartPos = Ibanlength(strText) + 2
                mEndPos = InStr(strText, "Zahlungsreferenz:") - 1
         
            CASE "*Auftraggeberreferenz:*"
                mStartPos = Ibanlength(strText) + 2
                mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
            
           CASE "*IBAN*Auftraggeberreferenz*"
                mStartPos = Ibanlength(strText) + 2
                mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
             
        END SELECT
    End If       

    mIntLength = mEndPos - mStartPos
    ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
     

End Function

However your logic and search strings make little sense.
If the text "Auftraggeberreferenz:" always has the colon on the end, the second statement will always fire as it catches the third statement as well...
 
ok this is that I got at present

Code: 
Public Function ReturnUMS01(strText As String) As String

    If strText Like "IBAN*" Then
        If strText Like "*Zahlungsreferenz*" Then
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Zahlungsreferenz:") - 1
            mIntLength = mEndPos - mStartPos
    
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
            Exit Function
            
        If strText Like "*Auftraggeberreferenz:*" Then
            mStartPos = Ibanlength(strText) + 2
            mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
            mIntLength = mEndPos - mStartPos
    
            ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
            Exit Function
        End If
        
        If strText Like "*IBAN*" Then
            If strText Like "*Zahlungsreferenz*" Then
                mStartPos = Ibanlength(strText) + 2
                mEndPos = InStr(strText, "Zahlungsreferenz:") - 1
                mIntLength = mEndPos - mStartPos
        
                ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
                Exit Function
                
            If strText Like "*Auftraggeberreferenz*" Then
                mStartPos = Ibanlength(strText) + 2
                mEndPos = InStr(strText, "Auftraggeberreferenz:") - 1
                mIntLength = mEndPos - mStartPos
        
                ReturnUMS01 = Mid(strText, mStartPos, mIntLength)
                Exit Function
            End If
        End If
    End If
    End If
    End If
    
End Function

It also need to be included an else at the end to return the same as strText in case there are other strings! But I have not come so far..


Here are some text and what need to be returned

Code: 
"Allianz IBAN: AT70 1000 6666 7777 8888 Creditor-ID: AT25AEV00000004433 0 Auftraggeberreferenz: AEV21355-E00695611 SEPA-Lastschrift A559725496 Albert REF: 1200021122706174766477677966"

Return: Allianz

IBAN: AT00 0000 0000 0010 0000 Adrian Hofer Malermeister Zahlungsreferenz: R-2022-26 REF: 42390220429EBP00000135080387

Adrian Hofer Malermeister


Ing. Mag. Dr. Friedrich Schneider IBAN: AT00 0000 0000 0000 0000 Auftraggeberreferenz: RNr: 2302, KdNr: 577 Zahlungsreferenz: RNr: 2302, KdNr: 577 REF: 42390220221EBP00000129749526

Ing. Mag. Dr. Friedrich Schneider

IBAN: AT00 0000 0000 0000 0000 Finanzamt Sometown Auftraggeberreferenz: 820907376 REF: 42390220518EBP00000136825765

Finanzamt



Code: 
Public Function Ibanlength(ByVal strText As String) As Integer
    If strText Like "*IBAN: AT*" Then
        Ibanlength = 30
    ElseIf strText Like "*IBAN: DE*" Then
        Ibanlength = 33
    ElseIf strText Like "*IBAN: IT*" Then
        Ibanlength = 39
    ElseIf strText Like "*IBAN: LU*" Then
        Ibanlength = 30
    ElseIf strText Like "*IBAN: CH*" Then
        Ibanlength = 32
    ElseIf strText Like "*IBAN: FI*" Then
        Ibanlength = 28
    ElseIf strText Like "*AT[0-9]*" Then
        Ibanlength = 20
    ElseIf strText Like "*DE[0-9]*" Then
        Ibanlength = 22
    Else
        Ibanlength = 0      'noch kontrollieren
    End If
End Function
 
How about NOT passing the string unless it has IBAN in it? :(
Save needless checks in the firest place?
 
Hi guys,

many thanks for your all help!
@arnel unfortunately it does not return all the requiered string with your function. :(

@Minty I get an error as in some cases there is mEndPos or mStartPos 0 wich is when thre is non included as mentioned in my posts
there are either IBAN at the beginning of the string or in the middle somewhere or none at all .. for that case I just like to return what has been
passt into the strText

So I guess it would need somewhere down the line I have been trying to accomplish with the if Statements
 
try this again:
Code: 
Public Function ReturnUMS01(strText As String) As String
    Dim i As Integer, j As Integer
    i = InStr(1, strText, "IBAN:")
    If i = 1 Then
        strText = Trim$(Mid$(strText, 31))
        i = InStr(1, strText, "Zahlungsreferenz:")
        If i <> 0 Then
            ReturnUMS01 = Trim$(Left$(strText, i - 1))
            Exit Function
        End If
        i = InStr(1, strText, "Auftraggeberreferenz:")
        If i <> 0 Then
            ReturnUMS01 = Trim$(Left$(strText, i - 1))
            Exit Function
        End If
    Else
        If i <> 0 Then
            ReturnUMS01 = Trim$(Left$(strText, i - 1))
        End If
    End If
End Function
 
@Gasman
yes I can do that but however there are only a few with no IBAN in the string

Either in the beginning of the string then I need to check if there is "Zahlungsreferenz" in it
or if there is "Auftraggeberreferenz in it

if there is IBAN somewhere else in the String
I need the Zahlungsreferenz
or the Auftraggreferenz

The IBAN Length is important because I can have payment from other Countries and including the IBAN and how it is formated it has a fixed length.
 
Note:
Code: 
If strText Like "IBAN*" Then
 ...
    If strText Like "*IBAN*" Then
.. the 2. statement is not possible (except IBAN appears several times in the text)
Like "IBAN*" exclude Like "*IBAN*"
 
Last edited:
Hi arnelgp,

this is nearly perfect :-)

However is it somehow in your Code possible if there is non of the above that it returns the string it has been passed in strText?

It is only one Data which is blank but it has none of the above requirement as mentioned!

Sorry is it possible? To have it returning the strText if the function returns a blank?

Cheers
 
Code: 
Public Function ReturnUMS01(Byval strText As String) As String
    Dim i As Integer, j As Integer
    ReturnUMS01 = strText
    i = InStr(1, strText, "IBAN:")
    If i = 1 Then
        strText = Trim$(Mid$(strText, 31))
        i = InStr(1, strText, "Zahlungsreferenz:")
        If i <> 0 Then
            ReturnUMS01 = Trim$(Left$(strText, i - 1))
            Exit Function
        End If
        i = InStr(1, strText, "Auftraggeberreferenz:")
        If i <> 0 Then
            ReturnUMS01 = Trim$(Left$(strText, i - 1))
            Exit Function
        End If
    Else
        If i <> 0 Then
            ReturnUMS01 = Trim$(Left$(strText, i - 1))
        End If
    End If
End Function
 
silentwolf said:
@Minty I get an error as in some cases there is mEndPos or mStartPos 0 wich is when thre is non included as mentioned in my posts
there are either IBAN at the beginning of the string or in the middle somewhere or none at all .. for that case I just like to return what has been
passt into the strText
Click to expand...
You can handle that in the Case Statement with a Case Else at the end.

Select Case stops checking the other conditions as soon as one is true, or uses the Case Else - it's much cleaner to read and more obvious what is happening.
 
Hi guys,

many many thanks to you all!!

@Minty I will try your function with an Case Else statement too!

Many thanks

@arnelgp
Yes that works now perfect!!
Cheers!!
 
If Arnelgp's function works then that's good.

I would put suitable comments in to it now whist you understand it, as when you revisit it in 6 months time to adjust it you won't know what to change or why.
 
Example:
Code: 
Public Function ReturnUMS01(strText As String) As String

    Dim StartPos As Long
    Dim TextLen As Long
    Dim IbanPos As Long
    Dim SearchStrings() As String
  
    IbanPos = InStr(1, strText, "IBAN:")
  
    If IbanPos = 0 Then
        ReturnUMS01 = vbNullString
        Exit Function
    End If
  
    If IbanPos > 1 Then
         ReturnUMS01 = Trim(Left(strText, IbanPos - 1))
         Exit Function
    End If
  
    StartPos = Ibanlength(strText) + 2
  
    SearchStrings = Split("Zahlungsreferenz:|Auftraggeberreferenz:", "|")
    TextLen = GetLengthBeforeSearchString(Mid(strText, StartPos + 1), SearchStrings)
    ReturnUMS01 = Mid(strText, StartPos, TextLen)
   
End Function

Private Function GetLengthBeforeSearchString(ByVal TextToCheck As String, ByRef SearchStringArray() As String)
 
    Dim i As Long
    Dim Pos As Long
 
    For i = LBound(SearchStringArray) To UBound(SearchStringArray)
        Pos = InStr(1, TextToCheck, SearchStringArray(i))
        If Pos > 0 Then
            GetLengthBeforeSearchString= Pos - 1
            Exit Function
        End If
    Next
 
End Function

Tests:
Code: 
'AccUnit:Row("Allianz IBAx: AT70 1000 6666 7777 8888 Creditor-ID: AT25AEV00000004433 0 Auftraggeberreferenz: AEV21355-E00695611 SEPA-Lastschrift A559725496 Albert REF: 1200021122706174766477677966", "").Name = "Without IBAN"
'AccUnit:Row("Allianz IBAN: AT70 1000 6666 7777 8888 Creditor-ID: AT25AEV00000004433 0 Auftraggeberreferenz: AEV21355-E00695611 SEPA-Lastschrift A559725496 Albert REF: 1200021122706174766477677966", "Allianz")
'AccUnit:Row("Ing. Mag. Dr. Friedrich Schneider IBAN: AT00 0000 0000 0000 0000 Auftraggeberreferenz: RNr: 2302, KdNr: 577 Zahlungsreferenz: RNr: 2302, KdNr: 577 REF: 42390220221EBP00000129749526", "Ing. Mag. Dr. Friedrich Schneider")
'AccUnit:Row("IBAN: AT00 0000 0000 0010 0000 Adrian Hofer Malermeister Zahlungsreferenz: R-2022-26 REF: 42390220429EBP00000135080387", "Adrian Hofer Malermeister")
'AccUnit:Row("IBAN: AT00 0000 0000 0000 0000 Finanzamt Sometown Auftraggeberreferenz: 820907376 REF: 42390220518EBP00000136825765", "Finanzamt")
Public Sub ReturnUMS01_CheckReturnValue(strText As String, ByVal Expected As String)
   ' Arrange
   Dim Actual As String
   ' Act
   Actual = ReturnUMS01(strText)
   ' Assert
   Assert.That Actual, Iz.StringCompare(StringCompareMode_vbNullStringEqualEmptyString).EqualTo(Expected)
End Sub
=> The 5th test with “Finanzamt” fails, as “Finanzamt Sometown” would be returned.
 
Last edited:
You must log in or register to reply here.

Similar threads

S
Help with Instr Function
Replies
18
Views
753
CJ_London
C
C
Sendkeys not working anymore
Replies
7
Views
1,433
NauticalGent
NauticalGent
M
Signature Image in GMail email body
Replies
7
Views
347
Marshall Brooks
M
N
Solved Slow cursor movement from parent form to child form
2
Replies
20
Views
1,185
Minty
Minty
M
Extract data from outlook body with VBA
Replies
1
Views
883
bastanu
bastanu

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom