List names, InputBox, check if name is on the list

Good morning Laurence000

...and welcome to the forum!!

Originally Posted by Laurence000

Can anyone give me a hint more, or help. I've tried many different things, but I can't get figure out the way to have the name variable which is entered in the input box to be compared to the 30 names on the list.

We're not really here to do your homework for you That's for you to do, with your teacher's assistance.

That said, we're not monsters - we've all struggled with stuff before and needed help. Try posting what you've already got and someone will give you a shove in the right direction.

DominicB

Hi and thanks for wanting to help. I understand you aint gonna do my exercises for me, so Ill show you what Ive done so far. That being said, this is for an Advance Excel class in Graduate Business School and the professor is going really fast...

So here is what Ive done so far: I made different attempts

Here is the 1st one:

Public Sub NameListCheck()
    Dim Name As String
    
    
    Name = InputBox("What is your name?")
        If Name = Range("A:A") Then
            MsgBox ("Your name is on the list")
        Else
            MsgBox ("Your Name is not on the list")
        End If
End Sub

This didnt work due to: If Name = Range("A:A") Then

So I continued and tried this (which was a bad idea):

Public Sub NameListCheck()
    Dim Name As String
    Dim x As Range
    
    Name = InputBox("What is your name?")
    For Each x in Range("A:A")

          If Name = x Then
            MsgBox ("Your name is on the list")
        Else
            MsgBox ("Your Name is not on the list")
        End If
   Next
End Sub

This didnt work either and the loop never ends so I had to terminate excel....

I tried a third thing:

Public Sub NameListCheck()
    Dim Name As String
    Dim x As Variant
    Dim found As Boolean
    
    
    Name = InputBox("What is your name?")
    
    For Each x In Range("A1:A30")
        If Name = x Then
            found = True
        Else
            found = False
        End If
    Next
    
        If found = True Then
            MsgBox ("Your name is on the list")
        Else
            MsgBox ("your name is not on the list")
        End If
        
End Sub

This continuously displays the msgbox "your name is not on the list" no matter if I write a name that is on the list or not.

I tried with the variable found as an integer either:

Public Sub NameListCheck()
    Dim Name As String
    Dim x As Variant
    Dim found As Integer
    
    
    Name = InputBox("What is your name?")
    
    For Each x In Range("A1:A30")
        If Name = x Then
            found = 1
        Else
            found = 2
        End If
    Next
      
    If found = 1 Then
            MsgBox ("Your name is on the list")
    Else
            MsgBox ("your name is not on the list")
        End If
        
End Sub

Still... same problem. I dont really know what to do with this...

Im sure the solution is super simple and I just don't think about this the right way (direction) so if anyone, wants to give me a hint or let me know what I am not doing properly here, I will be SUPER happy.

thanks a lot!

I'll take the last version. If the Name is found, you set the value of Found equal to one. But then you carry on checking each cell so if the next cell is not the Name you are looking for, you then set Found to 2. What you need to do is exit the loop if it's found:

Public Sub NameListCheck()
Dim Name As String
Dim x As Variant
Dim found As Integer


Name = InputBox("What is your name?")

' set default value
Found = 2
For Each x In Range("A1:A30")
If Name = x Then
' found it, so set value and exit loop
found = 1
Exit for
End If

Next x

If found = 1 Then
MsgBox "Your name is on the list"
Else
MsgBox "your name is not on the list"
End If

End Sub

Note that you should also not have parentheses around the MsgBox arguments. You only use parentheses (basically) if you are returning a value from a function or if you are using the Call statement (which is unnecessary).

Finally, you can actually make use of worksheet functions in VBA and skip the loop altogether:

Public Sub NameListCheck()
Dim Name As String


Name = InputBox("What is your name?")

If Application.WorksheetFunction.Countif(Range("A1:A30"), Name) > 0 Then
MsgBox "Your name is on the list"
Else
MsgBox "your name is not on the list"
End If

End Sub