I am trying to add names to a list which may contain names already. The
user enters the name in a box and then presses enter. I only want to
add a name to the list if it doesn't exists in the list already. This
is almost working, although the code seems to be picking up parts of
names already in the list. i.e. if I wanted to add the name "Ann" to
the list, but the list already contains the name "Annabel" then it says
the name already exists. Can anyone help me so that it only says the
name exists in the list if it is an exact match...the code i have sol
far is below...........Thanks

Sub input_staff_name_checks()

staff_name = UCase(Cells(15, 9).Value)

If Len(staff_name) 0 Then

Set rngLook = Worksheets("Project Codes & Staff
Names").Range("F5:F100")

For j = 5 To 500

If staff_name < Cells(j, 6).Value Then

Set rngFind = rngLook.Find(staff_name,
MatchCase:=True)

If rngFind Is Nothing Then
Call input_names *** this method here enters the
name in the list once the check has been done ***
Else
MsgBox ("Staff member's name is already on the
list!")
End If

Set ringFind = Nothing
Exit For

End If

Next j

Else
MsgBox ("Please enter a staff member's name")

End If

End Sub


--
teeb
------------------------------------------------------------------------
teeb's Profile: http://www.excelforum.com/member.php...o&userid=13172
View this thread: http://www.excelforum.com/showthread...hreadid=539206

Add LookAt:=xlWhole to the Find statement.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"teeb" wrote in message
...

I am trying to add names to a list which may contain names already. The
user enters the name in a box and then presses enter. I only want to
add a name to the list if it doesn't exists in the list already. This
is almost working, although the code seems to be picking up parts of
names already in the list. i.e. if I wanted to add the name "Ann" to
the list, but the list already contains the name "Annabel" then it says
the name already exists. Can anyone help me so that it only says the
name exists in the list if it is an exact match...the code i have sol
far is below...........Thanks

Sub input_staff_name_checks()

staff_name = UCase(Cells(15, 9).Value)

If Len(staff_name) 0 Then

Set rngLook = Worksheets("Project Codes & Staff
Names").Range("F5:F100")

For j = 5 To 500

If staff_name < Cells(j, 6).Value Then

Set rngFind = rngLook.Find(staff_name,
MatchCase:=True)

If rngFind Is Nothing Then
Call input_names *** this method here enters the
name in the list once the check has been done ***
Else
MsgBox ("Staff member's name is already on the
list!")
End If

Set ringFind = Nothing
Exit For

End If

Next j

Else
MsgBox ("Please enter a staff member's name")

End If

End Sub


--
teeb
------------------------------------------------------------------------
teeb's Profile:
http://www.excelforum.com/member.php...o&userid=13172
View this thread: http://www.excelforum.com/showthread...hreadid=539206

Try this:

Set rngFind = rngLook.Find(staff_name, LookAt:=xlWhole,
MatchCase:=True)

Regards,
Stefi

€žteeb€� ezt Ã*rta:


I am trying to add names to a list which may contain names already. The
user enters the name in a box and then presses enter. I only want to
add a name to the list if it doesn't exists in the list already. This
is almost working, although the code seems to be picking up parts of
names already in the list. i.e. if I wanted to add the name "Ann" to
the list, but the list already contains the name "Annabel" then it says
the name already exists. Can anyone help me so that it only says the
name exists in the list if it is an exact match...the code i have sol
far is below...........Thanks

Sub input_staff_name_checks()

staff_name = UCase(Cells(15, 9).Value)

If Len(staff_name) 0 Then

Set rngLook = Worksheets("Project Codes & Staff
Names").Range("F5:F100")

For j = 5 To 500

If staff_name < Cells(j, 6).Value Then

Set rngFind = rngLook.Find(staff_name,
MatchCase:=True)

If rngFind Is Nothing Then
Call input_names *** this method here enters the
name in the list once the check has been done ***
Else
MsgBox ("Staff member's name is already on the
list!")
End If

Set ringFind = Nothing
Exit For

End If

Next j

Else
MsgBox ("Please enter a staff member's name")

End If

End Sub


--
teeb
------------------------------------------------------------------------
teeb's Profile: http://www.excelforum.com/member.php...o&userid=13172
View this thread: http://www.excelforum.com/showthread...hreadid=539206