String testing in VBA returns wrong result

Joined
Jul 23, 2012
Messages
6
Hello,
I am working on a function that should compare a String
Code:
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

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

Legend
Joined
Jun 19, 2002
Messages
63,651
Maybe this

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,532
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Are you sure the case is exactly the same?
 
Joined
Jul 23, 2012
Messages
6
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)
 
Joined
Jul 23, 2012
Messages
6
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,532
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Does it work if you use:
Code:
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
 
Joined
Jul 23, 2012
Messages
6
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,532
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Then the data does not match. If you alter the code to:
Code:
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.
 
Joined
Jul 23, 2012
Messages
6
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!:rolleyes:

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

Forum statistics

Threads
1,085,495
Messages
5,384,007
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top