String testing in VBA returns wrong result

[Santiago Lester Clements]

Hello,
I am working on a function that should compare a String

originalName

to a list of names. I use a While loop with an if statement checking at every line of the list whether or not the content is identical to the string in argument.
However, when I run the function step by step, when I arrive at the line where the two strings are identical, the comparison still deems them as false and the loop continues to the end of the list.

The originalName is extracted from this list so it shouldn't have any extra spaces that would make the comparison return false. I have good notions of VBA, yet I can't find the mistake I made :-/
I attach my code for this function below,

thanks in advance

Public Function TrouverNom(originalName As String) As Integer
    Dim i As Integer
    Sheets("3-Planning par monteurs IT").Activate
    i = 7
    Dim presence As Boolean
    presence = False
    'Looping on the list of names, firstblank3 is a module variable indicating
    'the first blank cell on this particular list.
    While (presence = False And i <= firstblank3)
        'The error is at the line below
        If Range("B" & i).Value = originalName Then
        'The error is at the line above
        presence = True
        End If
        i = i + 1
    Wend
    TrouverNom = i - 1
    Sheets("2-Besoin chantier").Activate
End Function

 

[VoG]

Maybe this

Public Function TrouverNom(originalName As String) As Long
Dim Found As Range
Set Found = Sheets("3-Planning par monteurs IT").Columns("B").Find(what:=originalName, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    TrouverNom = 0
Else
    TrouverNom = Found.Row
End If
End Function

 

[RoryA]

Are you sure the case is exactly the same?

 

[Santiago Lester Clements]

Hello RoryA,
I checked it was exactly the same, and there is no difference in the case (All names are in format SURNAME Firstname, and the name I am comparing to the list originates in the list)

 

[Santiago Lester Clements]

Hello VoG,
thank you for your answer,
I tested your code, at execution the program finds Found is nothing, could it have anything to do with the fact that xlValues is at -4163 in my module?

 

[RoryA]

Does it work if you use:

Public Function TrouverNom(originalName As String) As Integer    Dim i As Integer
    Dim presence As Boolean
    i = 7
    presence = False
    'Looping on the list of names, firstblank3 is a module variable indicating
    'the first blank cell on this particular list.
    While (presence = False And i <= firstblank3)
        'The error is at the line below
        If Sheets("3-Planning par monteurs IT").Range("B" & i).Value = originalName Then
        'The error is at the line above
        presence = True
        End If
        i = i + 1
    Wend
    TrouverNom = i - 1
End Function

 

[Santiago Lester Clements]

Thanks for your answer,
The result is the same as with my first function, the name is not found in the list and the function returns the value of firstblank3.

 

[RoryA]

Then the data does not match. If you alter the code to:

Public Function TrouverNom(originalName As String) As Integer    Dim i As Integer
    Dim presence As Boolean
    i = 7
    presence = False
    'Looping on the list of names, firstblank3 is a module variable indicating
    'the first blank cell on this particular list.
    While (presence = False And i <= firstblank3)
        'The error is at the line below
        Debug.Print "Input: " & originalName & vbLf & "Output: " & Sheets("3-Planning par monteurs IT").Range("B" & i).Value
        If Sheets("3-Planning par monteurs IT").Range("B" & i).Value = originalName Then
        'The error is at the line above
        presence = True
        End If
        i = i + 1
    Wend
    TrouverNom = i - 1
End Function

then you will be able to see the values in the Immediate Window in the VBEditor to see what comparison is occurring.

 

[Santiago Lester Clements]

The immediate window showed that there was an extra space which I hadn't noticed!
I am terribly sorry, especially since I said I had carefully checked!

Thanks a lot for your time and all your answers, you've been a great help!

 

[Santiago Lester Clements]

I used

Trim(originalName)

to get rid of the extra-space at the end (I don't know how it appeared (-_-)' )
Thanks again